3 Month Rolling

[hkapadia]

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.

[#2]

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

F

[ecolomer]

See this example:

[saurabh5]

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)