Top Value for each day for a range of dates
I would like to be able to bring back an answer that shows the highest value within a measure for each day within a range of dates e.g. If on Monday we have values of 50, 45, 60, 20 and on Tuesday we have values of 30,50,25,15 and on Wednesday we have values of 90, 80, 65,75. I would like to have the answer show
Monday, Customer n, 60
Tuesday, Customer m, 50
Wednesday, Customer o, 90
Displaying the customer who made the largest order for each day.
I guess in essence filtering each day to bring back the row that matches the max value within that day.
Hope that makes sense.
Been trying a few things, but not getting very far.
Many thanks for looking at this.
Hi Jason, the easiest way is to use the group_max function I think. So create an formula called something like 'daily top customer' with the calculation
amount = group_max(amount, day_name)
(I am not sure what your fields are called, but this formula returns true when the amount fields is equal to the maximum of that day)
With this formula present you can add in your search something like
daily top customer = true
Jason Kapp Is 'sold' an aggregation by itself? TS does not allow you to nest aggregations (The error message is a bit misleading)....
One way to get around that is to put the original search (day, customer, sold) in a view and build a query on top of that view which does what I described above. That should work.