Average numebr of people in nested aggregation

Question & Answer
1

Hi all,
I am trying to do a line chart that shows the average number of people at each hour of the day in a store.
The values that I have to average are repeated multiple times for each hour and are different for each area of the store. For example:
timestamp area #people
yyyy-mm-01 12:00:00 Bar 3
yyyy-mm-01 12:00:00 Bar 3
yyyy-mm-01 12:00:00 Restaurant 5
yyyy-mm-01 12:00:00 Back 4
yyyy-mm-01 12:00:00 Back 4
yyyy-mm-02 12:00:00 Bar 10
yyyy-mm-02 12:00:00 Bar 10
yyyy-mm-02 12:00:00 Restaurant 20
yyyy-mm-02 12:00:00 Back 8
yyyy-mm-02 12:00:00 Back 8

I would like a line chart that for the value hour = 12 gives me the average number of people in total [(3+5+4)+(10+20+8)]/2. And to still give the possibility to drill down in each area and get:

  • for Bar the value (3+10)/2
  • for Restautant the value (5+20)/2
  • for Back the value (4+8)/2

I thought of using this approach:

  • max value grouping by timestamp and area
  • sum the values grouping by timestamp
    calculate the average.

I tried creating the following calculated field:

sum(max({#people},[{area},{timestamp}]),[{timestamp}])

but i get this error:
“Visual agg functions can’t be used operands of as Level Aware Calculation (LAC) agg functions”.

Do any of you have an idea?
Thanks in advance

2

Can you try this:

sum(maxOver({#people},[{area},{timestamp}], PRE_AGG))

You need the maxOver with PRE_AGG to specify that you need the max for each area and timestamp prior to aggregating the # of people on the visual.

You can read more about PRE_AGG and PRE_FILTER here:

Mastering these will help a lot when building calculated fields.

3

Thank you!
This allows me to have the sum along the days, is there any way to have the average of that sum? In the case of the example I would need to divide by 2 (distinct number of timestamps)

4

This should match the request:

sum(maxOver({#people},[{area},{timestamp}], PRE_AGG)) / distinct_count({timestamp})

5

I tried that but it doesn’t give me the result that I want. I think that what I want to do is the following:
sum(avg(max({#people},[area,timestamp])))

but it gives me the error “Nested of aggregate functions are not allowed”.
Do you happen to know how to solve this?

Thank you again

6

I am not seeing how this formula is not meeting your request:

sum(maxOver({#people},[{area},{timestamp}], PRE_AGG)) / distinct_count({timestamp})

Are you looking for the average amount of people at the establishment for every hour of the day, where we are calculating the average by looking at how many people are there at that hour for every day in your data set?

If so then this formula should work, by summing the max amount of people at each area for every day, and then dividing by the number of days (distinct_count of timestamp).

If this is not what you are intending to do, can you describe what the x_axis will be on the visual, and why the formula I posted above is not returning what you would expect?

7

Hi!
This is what I would like to achieve:

image
image1178×461 40.8 KB

Where the data is the average value of the sum of the maximum value per area and the x-axis are the hour of the day

8

Please let me know if this returns what you would expect:

avg(sumOver(maxOver({#people},[{area},{timestamp}], PRE_AGG),[{timestamp}], PRE_AGG))

We are finding the max for each area for each timestamp, selecting the sum of that max for each timestamp, and then taking the average of those sums.