MAT Calculation

[konidena]

Hi  Team,

Here is my sample data attached.

I have Integer field "MonthCode" is coming from data base in the format "YYYYMM".

Other fields I have

Product

StandardUnits

MarketSales

Here are my KPI calculations:

MarketShare: StandardUnits/MarketSales

EvolutionIndex: Current Year Market Share/Previous Year Market Share

MAT: Moving Annual Total is Sum of last 12 Months Standard Units

for example i am in the month 201606 (That is June) my MAT for June is  Sum of the sales data for the months July 2015 to Jun 2016.

Challenge I am facing: I have to show Evolution Index Verses Time for MAT

X-Axis Dimension should be of the format MonthYY (jul 15 to Jun 16)

Here the MAT for Jul 2015 is sum of the earlier 12 months data . That is Aug 14 to Jul 15

for Aug 15, Sep 14 to Aug 15. Like this, my x-axis should show the dimension values (Jun 15,jul 15, Aug 15, Sep 15, Oct 15, Nov 15, Dec 15, Jan 16, Feb 16, Mar 16, Apr 16, May 16, Jun 16).....This order will change when i moved to next month

Y-Axis should show Evolution index .

so my calculation for Evolution Index should be (Current Last 12 Months Market Share)/(Previous last 12 Months)

Example :      (MarketShare (jul 2015 to Jun 2016))/(MarketShare(Jul 2014 to Jun 2015))

I have used AsOfTable in application. But, I am not able to to get the x axis month values.

Regards

Srinivas

[sasikanth]

HI Srinivas,

please check the script once, the resident load

i understood like, Feb 15= sum( value for Feb 15)/sum( value for Feb 14)   and implemented the same through script

the below expression result should be distributed over MonthYear Dim Crct?

=(sum({<Month_Year={">$(=Date(AddMonths( max(New_Date) ,-12),'YYYYMM'))<=$(=Date(AddMonths( max(New_Date) ,-0),'YYYYMM'))"},Flag={'Org'},Year=,Month=>}MarketShare))

/

(sum({<Month_Year={">$(=Date(AddMonths( max(New_Date) ,-24),'YYYYMM'))<=$(=Date(AddMonths( max(New_Date) ,-12),'YYYYMM'))"},Flag={'Org'},Year=,Month=>}MarketShare))

we cannot get the result if we use the exp , so changed at script level

let me know if i am wrong,,

Thanks

Sasi

[konidena]

Hi Sasi,

Somehow, i am not able to fix the issue...attached my application with scrambled data.

Please check my data model now and help me to get the required output.

Regards

srinivas