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.

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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
  • Hi Misha,

    I think I have tried something like that and have just tried it again and I get a nonsense error message.

    Function group_max expects 1st argument to be DateTime

    Maybe there is a bug in the version I am using (5.3.1)?

  • 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.

  • Bit misleading - LOL. Yes it is an aggregation. Does that mean I am stuck?

  • No, in my previous reply I gave you an option to do it (using a view), so that should work for you.

  • Yes, I have resolved it using a view as suggested - thanks for your help!

    Like 1
  • I wonder if you could use the inline query to do this instead of creating a view? 

Like Follow
  • Status Answered
  • 9 mths agoLast active
  • 7Replies
  • 37Views
  • 3 Following