6 Replies Latest reply: May 10, 2017 7:58 AM by Elizabeth Viso RSS

    Filter Year via Script

    Elizabeth Viso

      Hello,

       

      I am pulling in data and have it set to bring in only data starting from 01/01/2016:

       

      [UMH Central Line Surveillance]:

      load * where [Created Date]>='2016-01-01 00:00';

       

      which is perfect, but now when I create a filter for the month/year I get all the years all the way up to 2024. How can I remove that?

       

      Thank you!

        • Re: Filter Year via Script
          Sunny Talwar

          What Years do you want to get? May be do this

           

          [UMH Central Line Surveillance]:

          load * where [Created Date]>='2016-01-01 00:00' and [Created Date] <= Today();

          • Re: Filter Year via Script
            Anil Babu Samineni

            Could you elaborate more on this? I've read last response as well.

              • Re: Filter Year via Script
                Elizabeth Viso

                Hi Anil,

                 

                I have a filter with the Month/Year starting from Jan 2016 but it is going all the way up to the year 2024. I only have data for last year and this year. I want to be able to have my filter only should last year and this year.

                 

                Make sense?

                  • Re: Filter Year via Script
                    Bill Markham

                    Do you have a Master Calendar going up to 2024 in your data model ?

                      • Re: Filter Year via Script
                        Elizabeth Viso

                        I do have a master calendar and I don't think it is going till 2024. This is what I have in the script:

                         

                        MinMaxDate:

                        Load

                          min([Get Date]) as MinDate,

                            max([Get Date]) as MaxDate

                        resident [UMH Central Line Surveillance];

                         

                        Let vMinDate = Peek('MinDate',0,'MinMaxDate'); 

                        Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

                        Let vToday = $(vMaxDate);

                         

                        drop table MinMaxDate;

                         

                        TempCal:

                        Load

                          date($(vMinDate) + rowno()-1) as TempDate

                        autoGenerate($(vMaxDate)-$(vMinDate)+1);

                         

                         

                        Mastercalendar:

                        Load

                          TempDate as [Get Date],

                            year(TempDate) as Year,

                            month(TempDate) as Month,

                            monthname(TempDate) as MonthYear,

                            day(TempDate) as Day,

                            weekday (TempDate) as WeekDay,

                            week(TempDate) as Week,

                            year(yearstart(TempDate,0,4)) as FiscalYear,   

                          'Q' & ceil(month(TempDate)/3) as Quarter,      /

                          InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

                          InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

                           

                        resident TempCal;

                         

                        drop table TempCal;

                         

                        [Monthly Inspection Status Temp]:

                        LOAD

                          Date(AddMonths($(vMinDate),IterNo()-1),'MMM YYYY' ) AS [Inspection Month],

                            Max([Get Date]) as [Max Date]

                        RESIDENT [UMH Central Line Surveillance]

                        While AddMonths($(vMinDate),IterNo()-1) <= $(vMaxDate);

                         

                         

                        DROP Field [Max Date] FROM [UMH Central Line Surveillance];