Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Creator III

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

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

1 Solution

Accepted Solutions
MVP

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))

16 Replies
MVP

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)

Creator III
Author

Sunny ,

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

remaining 4 months

MVP

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

Creator III
Author

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

MVP

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

Creator III
Author

Yeah Sunny

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

Creator III
Author

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

MVP

What are the correct values according to you?

Creator III
Author

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

Community Browser