Ranking according to highest sales problem..

[rohitians]

Hello,

I have a pivot chart where in I have different companies and their different divisions with sales every month,

Sales=

sum(Values)/1000000

and i want their ranks according to the sales value

Rank=Rank(SUM({$<FiscalYear={$(=max(FiscalYear))}>} [Values])/10000000)

but I am not getting proper ranking.

Please help if any coreection required.

Thanks and Regards,

Rohit

[Gysbert_Wassenaar]

Take a look at pivot table in the attached example. You may need to use an expression like in the last column.

[gaurav2017]

Hi,

Cheers Gysbert. I would have suggested the same solution.

Please close the issue as it has been answered correctly as desired. If there is any other thing you want to add up, please write it up.

Regards

[rohitians]

Hello,

The expression is working properly,

but when i include month as dimension i am not getting proper rankings,

wen i select a particular month it works properly,

in the selection if i select suppose abc company and it has 200 products it shows ranking 1-200

instead of the rank which it has if no company is selected..

Thanks and Regards,

Rohit

[israrkhan]

Dear Gysbert,

i go through with your sample document, and i am confused at a point,

kindly explain me, in your third expression you used rank(total sum(total <Region,SalesPerson> Sales))

confusions: i think total keyword ignored the diminsions,right? first you ignored the dimension using total keyword and then you used the <Region, SalesPerson> to group by them. i think if you dont use total then it will group by Region, and SalesPerson as they are dimension.

what is the difference please explain..

Kind Regards

Khan

[rohitians · Accepted solution]

Hello,

The expression is working properly,

but when i include month as dimension i am not getting proper rankings,

wen i select a particular month it works properly,

in the selection if i select suppose abc company and it has 200 products it shows ranking 1-200

instead of the rank which it has if no company is selected..

Thanks and Regards,

Rohit

I just ignored the Company selection in set analysis and it showed the static rank even when company was selected..i made following changes....

Rank=aggr(Rank(SUM({$<FiscalYear={$(=max(FiscalYear)),COMPANY=}>} [Values])/10000000),COMPANY)