Aggregate calculation

[#1]

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

[avinashelite]

Hi Dony,

Can you please provide the sample data.

[maleksafa]

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?

[#4]

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

[maleksafa]

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.

[#6]

Hi Malek,

Thanks for your help. I fixed the issues now.

Thanks,

Dony

[maleksafa]

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