Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Thanks for your input @stevedark .
I will work on with your new inputs and get back to you.