# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:  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  MVP

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

7 Replies  MVP

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  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....  MVP

I just modified your current expression brother.... I don't know what the difference is between Current and CYMONTH  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.....  MVP

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

Best,

Sunny  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...  MVP

No problem at all  