Aggregating Purchases by Hour and Day

[#1]

Hello --

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)
Apparel	10	Mon	8	Tues
Accessories	15	Tues	8	Tues
Cosmetics	17	Tues	8	Tues
Electronics	9	Sun	8	Tues

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.

Best,

Dan

[kaushiknsolanki]

Hi,

Can you help us to understand how did you got this numbers from your sample data.

Regards,

Kaushik Solanki

[#3]

Hi --

The above was just a sample of how I am looking for the data to be displayed.

Dan

[kaushiknsolanki]

Ok,

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.

Regards,

Kaushik Solanki

[#5]

Hi Kaushik,

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
Accessories	22	$34,431.00	16	$106,387	Sunday	$54,980.00	Tuesday	$258,383.00
Apparel	17	$31,359.00	16	$106,387	Tuesday	$43,541.00	Tuesday	$258,383.00
Cosmetics	15	$50,447.00	16	$106,387	Thursday	$135,867.00	Tuesday	$258,383.00
Electronics	16	$49,703.00	16	$106,387	Wednesday	$74,414.00	Tuesday	$258,383.00

Best,

Dan

[Digvijay_Singh · Accepted solution]

Check this out -

[#7]

Looks pretty amazing! I am checking out the solution now, thanks!

I will comment in a moment.

[Anonymous]

Hi Digvijay,

     Please share the method in plain text. I am using personalized edition and I am not able to open the QLikview file.

[Digvijay_Singh]

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]))

Thank,

[Anonymous]

Thank you so much Digvijay