topic Re: 3 Month Rolling in QlikView

3 Month Rolling

hkapadia — Tue, 06 Jan 2015 16:12:18 GMT

I have a table which I'm bringing from SQL into Qlik with columns

AccountNumber, Amount, Mon-YY (Nov-14, Oct-14 ....)

I want to display the 3 month rolling data in a table in Qlik based on a selection for Mon-YY from a list box

If the user selects 'Nov-14' for the month then

AccountNumber, Amount for Nov-14, Amount for Oct-14, Amount for Sep-14

If the user selects 'Jul-14' for the month then

AccountNumber, Amount for Jul-14, Amount for Jun-14, Amount for May-14

I used AccountNumber as the dimension & created 3 expressions for 3 amounts.

3 Expressions are

For selected month : MonthName(date(Only(MonthYear)))

For selected month-1 : MonthName(date(Only(MonthYear)-1))

For selected month-2 : MonthName(date(Only(MonthYear)-2))

The first 2 expressions work but the 3rd one doesn't give me the expected result.

Thanks in advance for the help.

Re: 3 Month Rolling

Tue, 06 Jan 2015 16:16:38 GMT

Hi,

I think solving this in the load script is easier.

Load your records

Create a Calendar and indicate which months you like to display with an indicator

use set analysis in order to retrieve the records that are valid based on the indicator

good luck

Re: 3 Month Rolling

ecolomer — Tue, 06 Jan 2015 18:43:56 GMT

See this example:

Re: 3 Month Rolling

saurabh5 — Tue, 06 Jan 2015 18:53:57 GMT

Create a calender table:

T_MAX_MIN_DATE:

LOAD Max(DATE) AS MaxDate , Min(DATE) AS MinDate
Resident Your table
;

LET vMinDate = Peek('MinDate') ;
LET vMaxDate = Peek('MaxDate') ;

Drop table T_MAX_MIN_DATE;
///////////////////////////////// Date Calender//////////////////////////
CALENDER:
LOAD DATE,
Year,
Month,
Month&'-'&Year as Month_YEar,
MonthNo,
AutoNumber (Year & MonthNo, 'MonthID') as MonthID;
LOAD Date($(vMinDate) + IterNo() -1) AS DATE,
Year($(vMinDate) + IterNo() -1) AS Year,
Month($(vMinDate + IterNo() -1) AS Month,

Num(Month($(vMinDate) + IterNo() -1)) AS MonthNo
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate) ;

now use Month id for for calculating rolling 3 months:

Count

({<
Year= , Month= , MonthID={">=$(=Max(MonthID)-2)<=$(=Max(MonthID_))"}
>}AccountNumber)