# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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  Community Browser