topic Re: How to get Top 3 Brands? in QlikView

How to get Top 3 Brands?

hemanthaanichet — Tue, 28 Mar 2017 17:52:26 GMT

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

Re: How to get Top 3 Brands?

sunny_talwar — Tue, 28 Mar 2017 17:57:33 GMT

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

Re: How to get Top 3 Brands?

hemanthaanichet — Tue, 28 Mar 2017 18:13:24 GMT

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

Re: How to get Top 3 Brands?

sunny_talwar — Tue, 28 Mar 2017 18:15:51 GMT

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

Re: How to get Top 3 Brands?

hemanthaanichet — Tue, 28 Mar 2017 18:27:54 GMT

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

Re: How to get Top 3 Brands?

sunny_talwar — Tue, 28 Mar 2017 18:41:56 GMT

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

Best,

Sunny

Re: How to get Top 3 Brands?

hemanthaanichet — Tue, 28 Mar 2017 18:54:50 GMT

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

Apologies for previous not closed thread...

Re: How to get Top 3 Brands?

sunny_talwar — Tue, 28 Mar 2017 18:58:08 GMT

No problem at all