topic Define Specific Time periods in QlikView

Define Specific Time periods

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Define Specific Time periods

sunny_talwar — Thu, 05 Jan 2017 13:22:08 GMT

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?

Re: Define Specific Time periods

Thu, 05 Jan 2017 13:36:14 GMT

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.

Re: Define Specific Time periods

sunny_talwar — Thu, 05 Jan 2017 16:37:34 GMT

Since these quarters are overlapping, it would be best if you use The As-Of Table

Re: Define Specific Time periods

sunny_talwar — Sat, 07 Jan 2017 04:39:34 GMT

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;