topic Re: To show top 3 Brands in QlikView

To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 15:54:48 GMT

Hi All,

I used below given expression in calculated dimension to show only top 3 brands

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

and used this expression in expression tab

FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand, PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}

Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),

-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))

Sum(TOTAL <AsOfPeriod>Aggr(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand, PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}

Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand, AsOfYear),

-Aggr(Only({<Brand=, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand, AsOfYear)), AsOfYear, Brand)))

This expression is work fine the selected year & month but not for previous for year & month and

This expression works fine when the brand is below 3 or equal to 3 for the selected period.... I mean, only sold brand for the selected period...

There are some other brand also sold in selected period ... The brand share totals 100% when i am trying to show only top 3...

Required output refer the screen shot consider only 2016 values

Attached the sample data

Re: To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 17:30:49 GMT

Hi All

I made a correction in calculated dimension my selected year & month show correct values but my previous year of same month not show correct values

=if(aggr(rank(

sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},PeriodType={'CYMONTH'}>}Sales)),Brand)<=3,Brand)

Can any one help me......

Re: To show top 3 Brands

Anil_Babu_Samineni — Thu, 30 Mar 2017 17:49:07 GMT

Seems you have done aggr before rank only. May be this?

If(Aggr(Rank(sum({<AsOfPeriod ={">=$(=Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY')) <= $(Date(Max(AsOfPeriod), 'MMM-YY'))"},PeriodType={'CYMONTH'}>}Sales))<=3, Brand), Brand)

Re: To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 18:05:24 GMT

No Brother ... its not working.... same values are showing

Get output

Re: To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 18:06:48 GMT

Required Ouput values are

Re: To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 18:16:16 GMT

Anil...

can help what is the error in this expression

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

when i tired to insert it with main one it has some error.....

Re: To show top 3 Brands

sunny_talwar — Thu, 30 Mar 2017 19:11:39 GMT

Try this

(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},

Brand = {"=Rank(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))}, Brand, PeriodType = {'CYMONTH'}, AsOfYear = {'$(=Max(AsOfYear))'}, AsOfMonth>}

Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),

-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))) <= 3"},

PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}

Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),

-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))

Sum({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}TOTAL <AsOfYear>Aggr(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}

Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand, AsOfYear),

-Aggr(Only({<Brand=, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand, AsOfYear)), AsOfYear, Brand)))*100

Re: To show top 3 Brands

hemanthaanichet — Thu, 30 Mar 2017 20:26:15 GMT

yeah brother.... its working fine ....thank you very much.........

Re: To show top 3 Brands

sunny_talwar — Fri, 31 Mar 2017 01:01:26 GMT

No problem