5 Replies Latest reply: Mar 26, 2015 11:01 AM by Jonathan Penuliar RSS

    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.

        • Re: Rolling 12 months button
          Jonathan Penuliar

          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

              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
                  Jonathan Penuliar

                  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

                      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
                          Jonathan Penuliar

                          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.