Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Not applicable

## Aggregating Purchases by Hour and Day

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 DivisionHourDayHour (All)Day (All)
Apparel10Mon8Tues
Accessories15Tues8Tues
Cosmetics17Tues8Tues
Electronics9Sun8Tues

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

1 Solution

Accepted Solutions

Check this out -

9 Replies

Hi,

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

Regards,

Kaushik Solanki

Regards,
Kaushik
Not applicable
Author

Hi --

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

Dan

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

Regards,
Kaushik
Not applicable
Author

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

Check this out -

Not applicable
Author

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

I will comment in a moment.

Anonymous
Not applicable
Author

Hi Digvijay,

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

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
Not applicable
Author

Thank you so much Digvijay

Community Browser