topic Re: How to do Rolling 12 months for latest 12 months with Two Dimensions: in QlikView

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

hemanthaanichet — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Thu, 02 Feb 2017 10:57:47 GMT

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)

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

hemanthaanichet — Thu, 02 Feb 2017 11:15:30 GMT

Sunny ,

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

remaining 4 months

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

sunny_talwar — Thu, 02 Feb 2017 12:04:20 GMT

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

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

hemanthaanichet — Thu, 02 Feb 2017 13:00:43 GMT

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

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

sunny_talwar — Thu, 02 Feb 2017 13:09:32 GMT

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

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

hemanthaanichet — Fri, 03 Feb 2017 04:41:54 GMT

Yeah Sunny

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

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

hemanthaanichet — Fri, 03 Feb 2017 11:59:19 GMT

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

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

sunny_talwar — Fri, 03 Feb 2017 12:00:56 GMT

What are the correct values according to you?

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

hemanthaanichet — Fri, 03 Feb 2017 12:52:20 GMT

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

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

sunny_talwar — Sun, 05 Feb 2017 21:17:25 GMT

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

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

hemanthaanichet — Mon, 06 Feb 2017 11:10:17 GMT

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

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

sunny_talwar — Mon, 06 Feb 2017 12:12:51 GMT

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)

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

hemanthaanichet — Mon, 06 Feb 2017 12:45:53 GMT

Yeah Sunny i will make changes and do work on it

Cheers..!

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

hemanthaanichet — Tue, 07 Feb 2017 07:15:40 GMT

Sunny it is working fine and thank you very much

Cheers...!

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

hemanthaanichet — Tue, 07 Feb 2017 10:47:36 GMT

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

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

vikasmahajan — Wed, 18 May 2022 11:22:04 GMT

Hi Sunny ,

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

Vikas