Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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
Digvijay_Singh

9 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi --

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

Dan

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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 / DivisonTop Hour / RevenueTop Hour / ALLTop Hour / Revenue ALLTop Day / DivisionTop Day / RevenueTop Day / ALLTop Day / Revenue ALL
Accessories22$34,431.0016 $106,387Sunday$54,980.00Tuesday$258,383.00
Apparel17$31,359.0016 $106,387Tuesday$43,541.00Tuesday$258,383.00
Cosmetics15$50,447.0016 $106,387Thursday$135,867.00Tuesday$258,383.00
Electronics16$49,703.0016 $106,387Wednesday$74,414.00Tuesday$258,383.00

Best,

Dan

Digvijay_Singh

Check this out -

Capture.PNG

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.

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

Thank you so much Digvijay