Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
Honored Contributor III

Re: Aggregating Purchases by Hour and Day

Check this out -

Capture.PNG

9 Replies

Re: Aggregating Purchases by Hour and Day

Hi,

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

Regards,

Kaushik Solanki

Not applicable

Re: Aggregating Purchases by Hour and Day

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

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

Not applicable

Re: Aggregating Purchases by Hour and Day

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
Honored Contributor III

Re: Aggregating Purchases by Hour and Day

Check this out -

Capture.PNG

Not applicable

Re: Aggregating Purchases by Hour and Day

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

I will comment in a moment.

rubandoss786
Contributor

Re: Aggregating Purchases by Hour and Day

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
Honored Contributor III

Re: Aggregating Purchases by Hour and Day

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,

rubandoss786
Contributor

Re: Aggregating Purchases by Hour and Day

Thank you so much Digvijay