Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am a new QlikView user and this is my first Qlik project ever.
I have a data set that contains monthly values for certain measures (costs, hit-rates, miles driven,...).
I am trying to calculate KPIs at the end of fiscal quarters. The KPIs are calculated using simple calculation on the measures (mostly averaging).
However, the KPIs are 12-months averages. So, for example, the value for KPI "x" in Q1-2016:
Average(x) = From Jun-16 to Jul-15
I have found good answers about how to create a master calendar and fiscal quarters, but I need to create "alternate quarters" that contain the last 12 months leading to the final month of the quarter.
Any help is greatly appreciated.
Kind regards
Alif
but I need to create "alternate quarters" that contain the last 12 months leading to the final month of the quarter.
Not sure I understand what you mean here?
Thank you for your reply, Sunny.
What I mean is that right now the quarters i have defined (using a master calendar) are normal quarters that have only three months.
However, the sector I work in is very seasonal. So I need to include the last 12 months' data when calculating a quarterly KPI to get rid of seasonality.
So what I need, is defined periods of time that have the data for the 12 months leading to the end of each quarter. So my time periods would be like this:
Period 1: Jul-15 to Jun-16 and I want to call it Q1-2017
Period 2: Oct-15 to Sep-16 and I want to call it Q2-2017
Period 3: Jan-16 to Dec-16 and I want to call it Q3-2017
Period 4: Apr-16 to Mar-17 and I want to call it Q4-2017
I called them "alternate quarters" because they are defined by the last month of every fiscal quarter.
Since these quarters are overlapping, it would be best if you use The As-Of Table
Here is a sample script to show how this can work
Table:
LOAD *,
MonthName(Date) as MonthYear,
'Q' & Ceil(If(Month(Date) > 4, (Month(Date)-3)/3, (Month(Date)+9)/3)) & '-' & If(Month(Date) > 4, Year(Date)+1, Year(Date)) as Quarter;
LOAD Date(MakeDate(2014, 12, 31) + IterNo()) as Date,
Ceil(Rand() * 100) * 100 as Sales
AutoGenerate 1
While MakeDate(2014, 12, 31) + IterNo() < MakeDate(2017, 4, 1);
AsOfTable:
Left Keep (Table)
LOAD DISTINCT MonthYear as AsOfMonthYear,
'Q' & Ceil(If(Month(Date) > 4, (Month(Date)-3)/3, (Month(Date)+9)/3)) & '-' & If(Month(Date) > 4, Year(Date)+1, Year(Date)) as AlternateQuarter,
MonthName(AddMonths(MonthYear, -IterNo() + 1)) as MonthYear
Resident Table
While IterNo() < 12;