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

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

View solution in original post

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

Capture.PNG

hemanthaanichet
Creator III
Creator III
Author

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?

Capture.PNG

hemanthaanichet
Creator III
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

sunny_talwar

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

hemanthaanichet
Creator III
Creator III
Author

Yeah Sunny

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

hemanthaanichet
Creator III
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




sunny_talwar

What are the correct values according to you?

hemanthaanichet
Creator III
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