Median of Counts

Question & Answer
1

Hi Team,
I feel like this is pretty easy and I’m getting stuck on something trivial. Apologies!

Ultimately I want to show a KPI card with the median number of views per user by product in a period (can be aggregated to each month if it can’t just use a date filter).

I have a table with attributes like so:

  1. user id
  2. event
  3. product
  4. event timestamp

So, for example, if the product was B, and we had 20 distinct users, we would sum the number of views each user had in that date period, and then take the median out of all users for the product(s) selected. That number would display in the KPI card.

I thought I would create 3 calculated fields:

  1. userView: if event = [view event] then 1 else 0
  2. userViewCount: sum(userView)
  3. medianUserView: median(userViewCount)

I get an error each time I try to get medians from a calculation with aggregation - count/sum etc…

Any help would be appreciated - thanks!

2

Hi,

have you used the Group and value
image

group product and rest in value

and calculate the field as at that time not in calculation. I think this will work for you.

image

regards,
Naveed Ali

1 Like
3

Thanks - I don’t believe KPI cards have the ability to group by.

4

You can’t have nested aggregations.

You could look to do something like this.

median(sumOver({abandon_time},[{queue_Name}],PRE_AGG))

Where you change the abandon time with your userView and the queueName with anything you want to group it by

1 Like