I am working through a problem and having some difficulty getting the result I am looking for.
Below is a simple table of what I am trying to achieve.
|Merchandise Division||Hour||Day||Hour (All)||Day (All)|
What I am looking for here is the top number of sales by hour and day per listed merchandise division. The goal would be to sum the orders by hour and day and output the hour and day which had the highest number of sales per division. I would also like to list the top hour across all division and days within the table as well.
I've tried a few set analyses but I think what I am trying to achieve is a bit more robust then I can handle.
Any thoughts on how one might achieve this?
I've attached a small data set which can better explain the inputs I am working with.
Can you help us to understand how did you got this numbers from your sample data.
If you can tell us the output which you need with correct numbers from the sample data you had given.
This will help us to think about the logic. Or if you have the logics for each column please let us know.
Below would be the correct answers for each division / total data set.
What I am trying to show is what hour / day were the top in terms of revenue for each division. Likewise, when combining all of the divisions what was the top hour and day.
|Top Hour / Divison||Top Hour / Revenue||Top Hour / ALL||Top Hour / Revenue ALL||Top Day / Division||Top Day / Revenue||Top Day / ALL||Top Day / Revenue ALL|
I can feel your pain , me too on personal edition, here are the expressions -
1. Aggr(if(Rank(Sum([Total Revenue]))=1,[Order Hour]),[Merchandise Division],[Order Hour])
2. Max(Aggr(sum([Total Revenue]),[Merchandise Division],[Order Hour]))
3. Aggr(NODISTINCT if(Rank(total Sum([Total Revenue]))=1,[Order Hour]),[Order Hour])
4. Max(total Aggr(sum([Total Revenue]),[Order Hour]))
5. Aggr(if(Rank(Sum([Total Revenue]))=1,[Order Day]),[Merchandise Division],[Order Day])
6. Max(Aggr(sum([Total Revenue]),[Merchandise Division],[Order Day]))
7. Aggr(NODISTINCT if(Rank(total Sum([Total Revenue]))=1,[Order Day]),[Order Day])
8. Max(total Aggr(sum([Total Revenue]),[Order Day]))