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]

May be try this:

Aggr((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, Brand, YearMonth)

[hemanthaanichet]

Sunny ,

In the chart only 8 months are displaying .....

remaining 4 months

[sunny_talwar]

I am not entirely sure, but can you check if this looks right?

[hemanthaanichet]

No Sunny there is some mis matching in the values

from my side iam sry i was a bit confused and make you to confused

[sunny_talwar]

Okay, so we need to wait to fix the issues at your end before we move forward?

[hemanthaanichet]

Yeah Sunny

I will get back to you once i fix the issue....

[hemanthaanichet]

My Requirement is quit bit complicated

I need to calculate incremental share values against YearMonth & Brand

Dimensions:

1. YearMonth

2. Brands

Note: Need show to latest 12 months

Expressions:

Formula:

(Current Year Sale /  Current Year Total Sale)

-

(Previous Year Sale / Previous Year Total Sale)

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

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

I wrote this expression,but the values are not correct

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

/

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

-

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

/

Sum(TOTAL{<YearMonth ={"$(='>=' & Date(AddMonths(Max(YearMonth), -23), 'MMM-YY') & '<=' & Date(AddMonths(Max(YearMonth), -12), 'MMM-YY'))"} ,Year=, Month=, Brand=>}Sales),12)),Brand,YearMonth)

Kindly have a look into the QVW

[sunny_talwar]

What are the correct values according to you?

[hemanthaanichet]

When i check

In  selection

year =2016, month=Jul, brand= uvw

Current Year:

Sales= 472  Total Sales =39174

Previous Year :

Sales= 673 Total Sales = 35427

As per formula

Manually:

(472/39174) -(673/35427) = -0.006948002

Expression Wise

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

=-0.0110126988