How to do Rolling 12 months for latest 12 months with Two Dimensions:

[hemanthaanichet]

Hi All,

I need to calculate the sales growth for latest 12 months comparing with previous 12 month against  Yearmonth & brand

Rolling 12 months for latest 12 months with Two Dimensions:

Dimensions:

1. YearMonth

2. Brand

Note: Need show to latest 12 months

Expressions:

Formula: (CY-PY)/PY

CY : Latest 12 months i.e.,(Aug-15 to Jul-16)

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

Expression Am  using:

(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -11), 'MMM-YY') & '<=' & Date(Max(YearMonth), 'MMM-YY'))"},

Year=, Month=>}Sales)

/

Above(Sum({<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"} ,

Year=, Month=>}Sales), 12)) - 1

My issue is

when iam using the second dimension (Brand) the expression part is not working and it showing like no data to display

Attached the sample data

Regards

Hemanth

[sunny_talwar]

I had to change the approach here because it seems that your data might have missing months within a brand where there are no sales available. I used the  The As-Of Table‌ approach to overcome this.

New Script

Table:

LOAD Date(MonthStart(YearMonth), 'MMM-YY') as YearMonth,

    Brand,

    Sales

FROM

[SAMPLE.xlsx]

(ooxml, embedded labels, table is Sheet1);

Calendar:

LOAD DISTINCT YearMonth,

    Year(YearMonth) AS Year,

    Num(Month(YearMonth)) AS MonthNum,

    Month(YearMonth) AS Month

Resident Table

Order By YearMonth;

AsOfCalendar:

LOAD DISTINCT YearMonth as AsOfYearMonth,

  AddMonths(YearMonth, -12) as YearMonth,

  'PY' as Flag

Resident Calendar;

Concatenate (AsOfCalendar)

LOAD DISTINCT YearMonth as AsOfYearMonth,

  YearMonth,

  'CY' as Flag

Resident Calendar;

Dimensions

1) AsOfYearMonth

2) Brand

Expression

RangeSum(

Sum({<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'CY'}>}Sales)

/

Sum(TOTAL {<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'CY'}>}Sales),

-Sum({<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'PY'}>}Sales)

/

Sum(TOTAL {<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'PY'}>}Sales))

[hemanthaanichet]

Hi Sunny,

script change and expression is working fine

But only one issue is

when I select

Year = 2015

month = July

Dimension in chart show from Aug-15 to Jul-16 instead of Aug-14 to Jul-15

[sunny_talwar · Accepted solution]

Created AsOfMonth and AsOfYear for selections and added these to the expression

RangeSum(

Sum({<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'CY'}, AsOfMonth, AsOfYear>}Sales)

/

Sum(TOTAL {<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'CY'}, AsOfMonth, AsOfYear>}Sales),

-Sum({<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'PY'}, AsOfMonth, AsOfYear>}Sales)

/

Sum(TOTAL {<AsOfYearMonth ={"$(='>=' & Date(AddMonths(Max(AsOfYearMonth), -11), 'MMM-YY') & '<=' & Date(Max(AsOfYearMonth), 'MMM-YY'))"},Year=, Month=, Brand=, Flag = {'PY'}, AsOfMonth, AsOfYear>}Sales))

[hemanthaanichet]

Yeah Sunny i will make changes and do work on it

Cheers..!

[hemanthaanichet]

Sunny it is working fine and thank you very much

Cheers...!

[hemanthaanichet]

is there any chance of getting top brand per month??

[vikasmahajan]

Hi Sunny ,

Please can you attach script in text file or can you create QVF for the same.

Vikas