Rolling 12 Months in Load Script

[Halesha_Bandri]

Hi Team,

I am new to Qlik sense. Currently I am facing issue while calculating Rolling 12 months in the load script.

Step1:= I have aggregated the data in "month level" by considering all the dimensions and here I am calculating the "document count" as like below.

LOAD MonthYear,
Country,
Region,
Brand,
[Therapy Area]
Count(Document_number) AS Cnt
Resident AR
GROUP BY MonthYear, Country, Region, Brand,[Therapy Area]
;

Step2 := For the output of Step1, I need to perform Rolling 12 months in Qlik sense based on all dimensions. 

In SQL we can easily achieve this.

Please find the below code snippet for same.

SUM(Cnt) OVER (Partition BY Country,Region,Brand,[Therapy Area] Order By MonthYear ASC ROWS BETWEEN 11 Preceding AND 0 Following) AS Rolling_12Month

Note := In the Partition By clause I am not considering MonthYear but I am considering that MonthYear dimension in Order by clause only. This will help me in getting the  desired result.

Is there any idea how can we achieve this in Qlik sense?

Qlik Sense Enterprise on Windows 

@hic  , @rwunderlich , @Oleg_Troyansky , @kaushiknsolanki , @sunny_talwar ,  @MK_QSL , @marcus_sommer ,

@stevedark , @rubenmarin , @PrashantSangle 

[stevedark]

Hi @Halesha_Bandri 

The way I would look to tackle this is by creating a new field in the load script, with either a Yes/No or a binary flag (depending on whether you want to use it in a filter in Sense).

In the load do the following:

LOAD
   MonthYear,
   if(MonthYear >= addmonths(monthstart(today()), -11), 'Yes', 'No') as [In Rolling 12],
   Year(MonthYear) as Year,
   etc.

Once you have this, the expression can use Set Analysis to get to the count for those 12 months:

sum({<[In Rolling 12]*={'Yes'}>}Cnt)

This depends on MonthYear being a valid date, if it is a string then you may need to convert with Date# first.

It is always better to do more calculations in the load script than to do it in the charts themselves, simply due to how often the calculations need to be carried out and whether the user will be waiting for them to happen.

I also tend to have a variable with a parameter with my various date parts.

// Set some constants

let vDateFormat = 'DD MMM YYYY';

let vMonthFormat = 'MMM-YY';

// Set a function for getting date parts built

set vDateParts = Date(DayStart([$1 Date]), 'DD MMM YYYY') as [$1 Date],

Date(Floor([$1 Date], 1/1440), 'DD MMM YYYY hh:mm') as [$1 Date Time],

Date(Floor([$1 Date], 1/24), 'DD MMM YYYY hh:mm') as [$1 Date Hour],

                    Hour([$1 Date]) as [$1 Hour],

                    Date(MonthStart([$1 Date]), 'MMM-YYYY') as [$1 Month],

                    Year([$1 Date]) as [$1 Year],

                    Month([$1 Date]) as [$1 Month Name],

                    Day([$1 Date]) as [$1 Day No],

                    WeekDay([$1 Date],0) as [$1 Weekday],

                    Date(weekstart([$1 Date],0,0), 'DD MMM YYYY') as [$1 Week Start],

                    'FY' & if(Month([$1 Date]) <= 3, (Year([$1 Date])-1) & '/' & (Year([$1 Date])-2000), (Year([$1 Date])) & '/' & (Year([$1 Date])-1999)) as [$1 Financial Year],

                    if(MonthStart([$1 Date]) = monthstart(today()) and [$1 Date] < today(), 'Yes', 'No') as [$1 In Month To Date],

                    if(weekstart([$1 Date],0,0) = weekstart(today(),0,0) and [$1 Date] < today(), 'Yes', 'No') as [$1 In Week To Date],

                    if(weekstart([$1 Date],0,0) = weekstart(today(),-1,0), 'Yes', 'No') as [$1 In Prior Week],

                    if(DayStart([$1 Date]) = today(), 'Yes', 'No') as [$1 Today],

                    if(DayStart([$1 Date]) = today()-7, 'Yes', 'No') as [$1 7 Days Prior],

                    today() - DayStart([$1 Date]) as [$1 Days Ago]

                    ;

This can then be called in the load script as follows:

LOAD
    $(vDateParts(Order)),
    $(vDateParts(Invoice)),

And it will create fields such as Order Month and Order 7 Days Prior and all of the same fields for Invoice dates as well.

You might also want to look at my post on Prior Period Comparisons, which has a lot of extra date stuff in it:
https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Prior-Period-Comparison-with-Set-A...

Hope that helps!

Steve

[Halesha_Bandri]

Thanks for your input @stevedark .

I will work on with your new inputs and get back to you.