topic Re: Aggregating Purchases by Hour and Day in QlikView

Aggregating Purchases by Hour and Day

Tue, 02 May 2017 12:05:56 GMT

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

Re: Aggregating Purchases by Hour and Day

kaushiknsolanki — Tue, 02 May 2017 18:10:01 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Aggregating Purchases by Hour and Day

Tue, 02 May 2017 18:11:55 GMT

Hi --

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

Dan

Re: Aggregating Purchases by Hour and Day

kaushiknsolanki — Tue, 02 May 2017 18:16:55 GMT

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

Re: Aggregating Purchases by Hour and Day

Tue, 02 May 2017 18:28:01 GMT

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

Re: Aggregating Purchases by Hour and Day

Digvijay_Singh — Tue, 02 May 2017 21:43:50 GMT

Check this out -

Re: Aggregating Purchases by Hour and Day

Tue, 02 May 2017 22:07:11 GMT

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

I will comment in a moment.

Re: Aggregating Purchases by Hour and Day

Anonymous — Wed, 03 May 2017 06:56:57 GMT

Hi Digvijay,

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

Re: Aggregating Purchases by Hour and Day

Digvijay_Singh — Wed, 03 May 2017 14:41:06 GMT

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,

Re: Aggregating Purchases by Hour and Day

Anonymous — Wed, 03 May 2017 19:45:22 GMT

Thank you so much Digvijay