Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rolling 12 months button

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.

5 Replies
jpenuliar
Valued Contributor III

Re: Rolling 12 months button

Hi Stijn,

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

Qlikview how to:

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

Not applicable

Re: Rolling 12 months button

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.

jpenuliar
Valued Contributor III

Re: Rolling 12 months button

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'

Not applicable

Re: Rolling 12 months button

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 , )

jpenuliar
Valued Contributor III

Re: Rolling 12 months button

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.