Getting the minimum value

[Alexander_Arozi]

Hello.

I am having some problems with getting the minimum value, and was hoping you guys could lead me in the right direction.

The goal is to create a Box & Whiskers chart, displaying data of customer interaction with our products. the min/max and median values should all be showing daily activity based on a months activity. so, our Charts will show what our lowest, highest daily values was for the last month.

Doing this, we are calculating unique user IDs every day. our database has a column for that, labeled user_id, and we extract the daily unique user count by creating a measure (called mA) of typ “count_distinct” based on that column.

My initial idea was to then create another measure of type “min”, “max” etc, based on the measure mA. But, as I’m well aware now, I cant aggregate measures that are already aggregated.

So, we created a ndt for the first measure, to flatten it and be able to do the boxplot calculations from that one. I tried the dimension and metric below, where test 1 gave me the minimum user_id (not the distinct count) and test 2 just gave me 1 (no idea how)

as of now, I’m just stuck since I do not know what to.

TL;DR How do I create a Minimum value out of an aggregateable measure.

thanks in advance!

[Dawid]

Try this:

sql: ${test1} / 2 ;;

You added the calculation inside a Looker reference to a field, which means Looker is looking for a field that’s named test1/2

If you already have an NDT, can you not do all the calculations there and just expose min, max, median, mean etc.?

[Alexander_Arozi]

Hi Dawid and thanks for your answer.

the ${test1/2} was more to show that I’ve tried it out with both ${test1} and ${test2}. both ${test1} and ${test2} are in the derived table, but they don’t give me the results I want when I reference them in the boxplot_min measure.

${test1} provides me with the smallest user_id and not the distinct number of user_ids.

${test2} provides me with the number 1, independent on how I slice n Dice the data.

[Dawid]

Ahh okay… that was confusing 😃 In this case I’m not sure if this is possible.

Can you show us that NDT? And also some test data and maybe what you need as a result?