topic Re: Re: Aggregate calculation in QlikView

Aggregate calculation

Wed, 03 Sep 2014 14:38:37 GMT

Hi,

I am trying to build a report with Manufacturer, Quantity Sold: Today, Week, Month and Best Week. I can easily make the report for Today, Week, Month, but I don't have any clue how to find the best weekly sales in this month?

Eg:

Manufacturer	Today Qty	Week Qty	Month Qty	Best Week Qty in this month
HP	5	20	100	28
Canon	8	30	170	47

Can anyone please suggest a solution for this?

Thanks,

Dony

Re: Aggregate calculation

avinashelite — Wed, 03 Sep 2014 14:40:41 GMT

Hi Dony,

Can you please provide the sample data.

Re: Aggregate calculation

maleksafa — Wed, 03 Sep 2014 14:41:58 GMT

you should use the rank function, and aggregate the sum(Week Qty) by Month.

Can you upload a small document, to check the dimensions properly?

Re: Aggregate calculation

Wed, 03 Sep 2014 15:31:15 GMT

Hi Malek Safa,

Thanks for you reply.

I can make a report like this from your suggestion

Week	Quantity	Rank
35	504	1
36	363	2
33	300	3
32	280	4
30	270	5
31	260	6
29	255	7
28	240	8
34	238	9
27	235	10

Week: Dimension

Quantity: Sum({<Date={'>$(=QuarterStart(today())) <$(vTodayThisYear)'}>}[Invoiced Quantity])

Rank: RANK(Sum({<Date={'>$(=QuarterStart(today())) <$(vTodayThisYear)'}>}[Invoiced Quantity]))

From This I need to find only quantity 504( ie, Rank=1).

Can you please help me to do this by using aggr and Rank function as you have suggested?

Thanks,

Dony

Re: Re: Aggregate calculation

maleksafa — Thu, 04 Sep 2014 07:58:50 GMT

Hi,

i have uploaded a document showing how does the rank and aggr work together, very similar to the one that you want, you only have to replace the fields.

please tell me if this is what you need.

Re: Re: Aggregate calculation

Thu, 04 Sep 2014 13:58:09 GMT

Hi Malek,

Thanks for your help. I fixed the issues now.

Thanks,

Dony

Re: Re: Aggregate calculation

maleksafa — Thu, 04 Sep 2014 14:02:36 GMT

Great, then please close this topic to answered and by marking answers as helpful or correct answer.

Week	Quantity	Rank
35	504	1
36	363	2
33	300	3
32	280	4
30	270	5
31	260	6
29	255	7
28	240	8
34	238	9
27	235	10

Week	Quantity	Rank
35	504	1
36	363	2
33	300	3
32	280	4
30	270	5
31	260	6
29	255	7
28	240	8
34	238	9
27	235	10

Week	Quantity	Rank
35	504	1
36	363	2
33	300	3
32	280	4
30	270	5
31	260	6
29	255	7
28	240	8
34	238	9
27	235	10