topic Re: Previous 'n' months data calculation in QlikView

Previous 'n' months data calculation

Fri, 26 Aug 2011 06:46:40 GMT

Hi,

My requirement is to show previous 'n' months data in a chart.

There should be a list box with choices as 1, 2, 3, ...10; and depending on user selection that much previous months data with respect to current date should get displayed.

I have read all posts about rolling 3 months or rolling 13 months data and that hardcoding works just fine.

But here I dont want to hardcode values, instead user should be able to select how many previous months data he wishes to see.

Please help me out.

Re: Previous 'n' months data calculation

gandalfgray — Fri, 26 Aug 2011 07:24:56 GMT

As always there is more than one way to do it.

You can try this:

Create two variables, ie vMonthsInput and vCalculatedDate
Create an Input Box for vMonthsInput
Add two event triggers on vMonthsInput, one on OnChange and one on OnInput
1. They should have the same action: "Set Variable" and you should set the variable vCalculatedDate to: =AddMonths(today(),-vMonthsInput )
Use this set expression in the chart: {<Date={">$(vCalculatedDate)"}>}

This will select Dates that are within vMonthsInput months from today.

If you want to select Dates starting from the first of the month that was x months before today, you have to adjust the calculation in 3.1 above.

hth/gg

Re: Previous 'n' months data calculation

johnw — Fri, 26 Aug 2011 23:37:46 GMT

The attached example lets you select what rolling period to use for each month using a slider. The selection when it comes up includes 0, 1 and 2 months back, so it is a rolling 3 months average including the current month. Want a rolling 12 months average? Drag the right side of the slider over to 12. Want to exclude the current month, so it's a rolling previous 12 months? Drag the slider one to the right so you're including months 1-13 back.

Re: Previous 'n' months data calculation

Mon, 29 Aug 2011 06:11:04 GMT

Hi John,

I have personnel edition of qlikview, so I cannot open your file

@GandalfGray,

Currently I am showing data on lat 13 Month's month end in chart. I want to make the number of months dynamic.

When I use calculations suggested by you with today() it just works fine. But when I use it with Max(Date), it displays nothing.

I want to use that calculation with Max(Date).

Also I am not much familier with adding events. Please help me out.

(In my database, date is in 'YYYY-MM-DD' format and I have qlikview 9.)

Re: Previous 'n' months data calculation

johnw — Mon, 29 Aug 2011 15:39:53 GMT

rohitsant87 wrote:
I have personnel edition of qlikview, so I cannot open your file

OK, here's the script:

[Main]:
LOAD *
,date(monthstart(Date),'MMM YYYY') as MonthYear
;
LOAD
date(date#(20080101,'YYYYMMDD')+floor(rand()*1000)) as Date
,ceil(rand()*100) as Revenue
AUTOGENERATE 500
;

[Date Linkage]:
LOAD DISTINCT MonthYear
RESIDENT [Main]
;
LEFT JOIN ([Date Linkage])
LOAD MonthYear as AsOfMonthYear
RESIDENT [Date Linkage]
;
INNER JOIN ([Date Linkage])
LOAD *
,round((AsOfMonthYear-MonthYear)/30.436875) as MonthsBack
RESIDENT [Date Linkage]
WHERE MonthYear <= AsOfMonthYear
;

For the chart:

Dimension = AsOfMonthYear
Expression = sum(Revenue)/count(MonthsBack)

And beneath it there's a slider:

Field = MonthsBack
Multi Value
Continuous/Numeric
Static Step = 1