5 Replies Latest reply: May 10, 2017 2:58 AM by Fabrizio Giorgio RSS

    Calendar/Slider object with Master Calendar not filtering data

    Fabrizio Giorgio

      Hi all,

       

      i am creating a report that needs to be filtered by min date / max date based on a field SBISOEnterDate.

       

      I have created the master calendar, and the min/max variables seem to be working but i am not getting any filtering on my results...

       

      anyone have any ideas to offer? Thanks in advance!! Fab...

       

      Here are some screen shots:

      variables.JPG

      Tables.JPG

      MaxSOEnterDate.JPG

      MinSOEnterDate.JPG

       

      Script is as follows:

       

      QuartersMap:

      MAPPING LOAD * Inline [

      Month, Q

      1, Q3

      2, Q3

      3, Q3

      4, Q4

      5, Q4

      6, Q4

      7, Q1

      8, Q1

      9, Q1

      10, Q2

      11, Q2

      12, Q2

      ];

       

       

      Temp: 

      Load 

                     min(SBISOEnterDate) as minDate, 

                     max(SBISOEnterDate) as maxDate 

      Resident SBISOrder; 

       

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

                     week(TempDate) As Week, 

                     Year(TempDate) As Year, 

                     Month(TempDate) As Month, 

                     Day(TempDate) As Day, 

                     YeartoDate(TempDate)*-1 as CurYTDFlag, 

                     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

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

                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                     WeekDay(TempDate) as WeekDay 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar;