How to get Top 3 Brands?

[hemanthaanichet]

Hi

i need to get Top 3 Brands

i used this expression in Dimension

=if(aggr(rank(sum({<AsOfYear={'$(=max(AsOfYear))'}>}Sales)),Brand)<=3,Brand)

Expression:

(Sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},

PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales)

/

Sum(Total<AsOfPeriod>{<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},

PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth,Brand>}Sales))*100

Above expression works fine, when selected period has only 3 brands. if the selected period has more than 3 brands the values are not correct

for example

Jan 2016 has only 3 brands whose share are A=45% ,B=35%, C=20% and the sum is equal to 100%

if suppose the same Jan -2016 has 5 brands whose share are A=35%, B=25%, C=20% ,D=15%, E=5% .

i need only top 3 brands  A=35%, B=25%, C=20% and the sum should  be 80% but not 100%

Attached the sample data

Regards

Hemanth

[sunny_talwar · Accepted solution]

Try this

(Sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"}, Brand = {"=rank(sum({<AsOfYear={'$(=max(AsOfYear))'}>}Sales)) < 4"},

PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales)

/

Sum(Total<AsOfPeriod>{<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},

PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth,Brand>}Sales))*100

With Brand as the dimension

[hemanthaanichet]

Hi Brother,

Slight Change in the Expression

(Sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"}, Brand = {"=rank(sum({<AsOfYear={'$(=max(AsOfYear))'}>}Sales)) < 4"},

PeriodType = {'Current'}, AsOfYear, AsOfMonth>}Sales)

/

Sum(Total<AsOfPeriod>{<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},

PeriodType = {'Current'}, AsOfYear, AsOfMonth,Brand>}Sales))*100

But it working fine i think let me go a check bro....

[sunny_talwar]

I just modified your current expression brother.... I don't know what the difference is between Current and CYMONTH

[hemanthaanichet]

Its just flag brother...

Period type =CYMONTH then  Jan-16 will have Sum( sales) of latest 12 months

Period Type = Current  then Jan-16 will have Sum(Sales) of selected month

its woking fine brother..... Thanks alot brother.....

[sunny_talwar]

Awesome... close the thread in that case....

Best,

Sunny

[hemanthaanichet]

Yeah sure brother once I implemented it in the original app I will surely close the thread brother...

Apologies for previous not closed thread...

[sunny_talwar]

No problem at all