Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
hemanthaanichet
Creator III
Creator III

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

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


1 Solution

Accepted Solutions
hemanthaanichet
Creator III
Creator III
Author

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

View solution in original post

1 Reply
hemanthaanichet
Creator III
Creator III
Author

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