How to get Top 3 Brands for MAT Calculation with Two Dimension

hemanthaanichet — Wed, 29 Mar 2017 16:26:18 GMT

Hi Guys,

I need to calculate MAT for the available data and has to show Top 3 brands for the selected period

Moving Average Turnover (MAT):

Dimensions:

1. Year

2. Brand

Formula:

(Current Year MAT Sale - Previous Year MAT Sale)

(Current Year MAT Total Sale - Previous Year MAT Total Sale)

Current Year : latest 12 months i.e.,(Aug-15 to Jul-16)

Previous Year : previous latest 12 months i.e.,(Aug-14 to Jul-15)

Expression output value should be plotted in latest year i.e., 2016

The above MAT solved with the below expression:

((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))

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

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

-Aggr(Only({<Brand, PeriodType = {'PYMONTH'}, 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))

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

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

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

When I need to get the Top 3 Brands, Output values are not correct when selected period has more than 3 brands. if the selected period has 3 brands the values are correct

Attached the Sample Data

Re: How to get Top 3 Brands for MAT Calculation with Two Dimension

hemanthaanichet — Fri, 31 Mar 2017 09:14:40 GMT

Solved the solution using 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)))

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

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

Brand, PeriodType = {'PYMONTH'}, AsOfYear = {'$(=Max(AsOfYear))'}, AsOfMonth>}

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

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

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

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

-Aggr(Only({<Brand, PeriodType = {'PYMONTH'}, 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)))

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

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

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

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

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

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

topic Re: How to get Top 3 Brands for MAT Calculation with Two Dimension in QlikView

How to get Top 3 Brands for MAT Calculation with Two Dimension

Re: How to get Top 3 Brands for MAT Calculation with Two Dimension