Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hemanthaanichet
Creator III
Creator III

How to get Top 3 Brands?

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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