topic Re: Rolling 12 months button in QlikView

Rolling 12 months button

Wed, 25 Mar 2015 10:25:32 GMT

Hello all,

I'm fairly new at qlikview and I got a request to create a button/selection field which automatically selects the last 12 months.

If no year is selected it takes the last 12 months but if you select a year (and month) it needs to take the 12 months before.

I would prefer to include the selected month.

I found several discussion but not really the same I need.

I need it as a button /selection field at the top which would influence all charts in the project.

Thus far I created a button and I added an action 'Select in Field'.

The field is my date.

and as search string I have: =if(v12M = 1, '>' & date(AddYears(Now(),-1)) & '<= ' & date(now()), )

This gives ma a selection of the last 12 months from Today.

I use the variable to set color and turn it off and on.

But now I need it to add the part which makes it variable to my selection.

I'm pretty sure it is with set analysis. But my attempts to add it have failed.

Could someone help me out with this?

thank you very much.

Re: Rolling 12 months button

jpenuliar — Wed, 25 Mar 2015 11:54:10 GMT

Hi Stijn,

The link below has a good example of Rolling Months using Flag.

Qlikview how to:

http://community.qlik.com/docs/DOC-5486

Re: Rolling 12 months button

Thu, 26 Mar 2015 07:42:14 GMT

Hello,

Thank you for your reply.

I checked the document and this applies the 12 months to 1 chart.

I would need a button and when this button is active it needs to select the last 12 months.

When no year(and month) is selected it takes from today otherwise the last 12 months from that date.

I created the flag Rolling12MthFlag like described in the document. But then I still don't know how to setup my button.

=if(v12M = 1, '>=' & date(AddYears(Now(),-1)) & '<= ' & date(now()), )

I know the Now() needs to be replaced by something but I can't figure out what.

thank you for your help.

Re: Rolling 12 months button

jpenuliar — Thu, 26 Mar 2015 09:16:21 GMT

you can add a "Select in Field" Action to your button property.

go to Button Property>> Action : "Select in Field" ; Field : Rolling12MthFlag ; Search String : 'Whatever value representing prev 12Mths'

Re: Rolling 12 months button

Thu, 26 Mar 2015 14:06:46 GMT

Hello,

Indeed I didn't came up with that I was still doing my selection on the date field.

but still I'm missing something. If I add table with the dates and Rolling12MthFlag.

I see a status 1 for every date that is in the last 12 months. But when I select a year the flag is not recalculated.

Will a script recalculate the flag when you make a selection?

I would give you the document but

This is the Script for the calendar I have.

Temp_Dates:

Load

min(RejectionDate) as MinDate,

max(RejectionDate) as MaxDate

Resident RejectionHeaders ;

LET varMinDate = num(Peek('MinDate', 0, 'Temp_Dates'));

LET varMaxDate = num(Peek('MaxDate', 0, 'Temp_Dates'));//makedate(Year(Peek('Datum', -1, 'Temp_Dates')),12,31)//num();

LET vToday = $(varMaxDate);

Drop table Temp_Dates;

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate)+IterNo()-1 as Num,

Date($(varMinDate)+IterNo()-1) as TempDate

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ******************

Calendar:

LOAD

TempDate as RejectionDate,

Year(TempDate) as Year,

Month(TempDate) as Month,

Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,

if(makedate(2000, Month(TempDate), Day(TempDate)) < makedate(2000, month(Today()),

Day(Today())),

'Year-to-date') as YTD,

Week(TempDate) as Week,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

Weekstart(TempDate) as Weekstart,

Weekend(TempDate) as Weekend,

Day(TempDate) as Day,

if(TempDate>=monthstart(AddMonths($(vToday), -12)) AND TempDate<monthstart($(vToday)), 1, 0) as Rolling12MthFlag

RESIDENT TempCalendar

ORDER BY TempDate Asc;

Drop table TempCalendar;

My button has

=if(v12M = 1,'= ' & 1 , )

Re: Rolling 12 months button

jpenuliar — Thu, 26 Mar 2015 15:01:10 GMT

The flag will only be calculated during reload,

If you want the previous year recalculated based on selected date, have a look at

"Working With Dates" tab on the same "Qlikview How To" document.

You can go by having a Startdate and EndDate variables,

The Tutorial has made use of 2 Calendar objects to assigned values to the variables,

in your case you can have the EndDate and calculated the StartDate with the AddYears()

The Select in Field Action on Buttons still are in play.