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

[hemanthaanichet]

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

[hemanthaanichet · Accepted solution]

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