0 Replies Latest reply: Nov 9, 2016 12:58 AM by Karthik Valluri RSS

    Need 1 filter pane associated to 2 dates

    Karthik Valluri

      Hi, I have created a 2 filter panes  having distinct values for time period like MOM,QOQ and YOY. Now based on the selection i make here i need 2 calendars( i again used filter panes for dates), one for FromDate and another for ToDate.

       

      The initial struggle encountered was whenever i select MOM and Apr-09 , i see that the next filter pane too has Apr-09... to overcome this i used Alternate States in qliksense and now it works fine. However the requirement now is to not have 2 filter panes for types but rather have only one. So, selecting one type( say MOM) should make the From and To calendars to reflect only Month values without any association between them so that the user can select one month from calendar1 and another month from calendar2, please advice on how to achieve this?

       

      Listboxes.jpg

       

       

       

      Temp:

      Load

                     min([Order Date]) as minDate, // Replace DateKey with your Date key field

                     max([Order Date]) as maxDate  // Replace DateKey with your Date key field

      Resident t; // Replace SourceTable with the name of the table containing the DateKey field

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;

       

      TempCalendar:

      LOAD

                     $(varMinDate) + Iterno()-1 As Num,

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

                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                    

       

       

      MasterCalendar:

      LOAD

                     TempDate AS DateKey,

                     Year(TempDate) as Year,

                     Month(TempDate) as Month,

                     Day(TempDate) as Day,

                     ApplyMap('QuartersMap', month(TempDate), Null())

                     as Quarter

       

       

      Resident TempCalendar;

       

       

      Listbox:

      LOAD * Inline [

      type,id

      'YOY',1

      'QOQ',2

      'MOM',3

      ];

       

       

      CalendarYear:

      LOAD

      text(year(DateKey)) as DateKey1,

      1 as id

       

       

      Resident MasterCalendar;

       

       

      Concatenate(CalendarYear)

      LOAD

      text((Date(monthstart(DateKey), 'MMM-YY'))) as DateKey1,

      3 as id

       

       

      Resident MasterCalendar;

       

       

      Concatenate(CalendarYear)

      LOAD

      'Q' & Ceil(Month(DateKey)/3)as DateKey1,

      2 as id

      Resident MasterCalendar;

       

       

      DROP TABLE MasterCalendar;

      DROP TABLE TempCalendar;