7 Replies Latest reply: Apr 2, 2018 2:28 AM by Vishal Arote RSS

    Last x days filter not working

    Shuhel Miah

      I'm using the following query to generate a filter to restrict data to the selected number of days:

       

      IF(TODAY()-[Creation Date]<=1,'Last 1 day',
          IF(TODAY()-[Creation Date]<=3,'Last 3 days',
              IF(TODAY()-[Creation Date]<=7,'Last 7 days',
                  IF(TODAY()-[Creation Date]<=14,'Last 14 days',
                      IF(TODAY()-[Creation Date]<=30,'Last 30 days',
          )))))
      

       

      However, it isn't performing as expected. For instance, if I select 'Last 7 days' it, only returns records between 3 to 7 days; I need to select the previous 2 selections to get the full 0 - 7 days I require.

        • Re: Last x days filter not working
          Lakshmikandh Karthikeyan

          Using this extension define a variable vDays and select type as Dropdown  GitHub - erikwett/qsVariable: Variable extension for Qlik Sense

          define alternatives in that extension like this,

          1,'Last 1 day'

          3,'Last 3 days'

          7,'Last 7 days'

          14,'Last 14 days'

          30,'Last 30 days'


          and apply condition TODAY()-[Creation Date]<=vDays in your charts

          This will filter the value the way you want.

          • Re: Last x days filter not working
            Jonathan Dienst

            Well you can only have a single value in one field, so if an item is already assigned to 'Last 3 days', it cannot also be assigned to 'Last 7 days'. You will need to create a separate field for each of the time windows:

             

            If(Today() - [Creation Date] <= 1, 1, 0) as [Last 1 day],

            If(Today() - [Creation Date] <= 3, 1, 0) as [Last 3 days],

            If(Today() - [Creation Date] <= 7, 1, 0) as [Last 7 days],

            ....

            • Re: Last x days filter not working
              Nadeem Shaikh

              Hi,

               

              Jonathan is right or simply make a selection on all the 3 buckets ,'Last 1 day','Last 3 days','Last 7 days' to get your last 7 days output.

               

              Regards,

              Nadeem

              • Re: Last x days filter not working
                thomas le gall

                You could maybe use this script, but you have to access the script

                 

                Your_Initial_Data:

                LOAD

                Field1,

                Field2,

                TODAY()-[Creation Date] as Days                          // Add this calculation

                FROM Your_Initial_Source;

                 

                Intervalmatch(Days)

                LOAD * INLINE [

                From, To, LastDays

                0, 1, Last 1 day

                0, 3, Last 3 days

                0, 7, last 7 days

                0, 14, last 14 days

                0, 30, last 30 days

                ];

                 

                Drop fields From, To;

                • Re: Last x days filter not working
                  Michael Solomovich

                  Assuming you have a Calendar table with the Creation Date field.  In this case, it makes sense to create a separate "filter" table:

                   

                   

                  FilterTable:

                  LOAD DISTINCT

                  [Creation Date],

                  'All' as Filter

                  RESIDENT Calendar;

                   

                  CONCATENATE (FilterTable)

                  LOAD DISTINCT

                  [Creation Date],

                  'Last 1 day' as Filter

                  RESIDENT Calendar

                  WHERE TODAY()-[Creation Date]<=1;

                   

                  CONCATENATE (FilterTable)

                  LOAD DISTINCT

                  [Creation Date],

                  'Last 3 days' as Filter

                  RESIDENT Calendar

                  WHERE TODAY()-[Creation Date]<=3;

                  .....

                   

                  CONCATENATE (FilterTable)

                  LOAD DISTINCT

                  [Creation Date],

                  'Last 30 days' as Filter

                  RESIDENT Calendar

                  WHERE TODAY()-[Creation Date]<=30;

                    • Re: Last x days filter not working
                      Shuhel Miah

                      I was trying to avoid that approach as the comparison is contingent on the data being refreshed daily. i.e. the last x day would be relative to the refresh date.

                      • Re: Last x days filter not working
                        Vishal Arote

                        Try this, this works.

                         

                        e.g.

                         

                        FilterTable:

                         

                         

                        LOAD DISTINCT

                        Date,

                        'All' as Filter

                        RESIDENT Data1;

                         

                         

                        CONCATENATE (FilterTable)

                        LOAD DISTINCT

                        Date,

                        'Last 1 day' as Filter

                        RESIDENT Data1

                        WHERE Date>=Daystart(TODAY(),-1) and Date<today();

                         

                        CONCATENATE (FilterTable)

                        LOAD DISTINCT

                        Date,

                        'Last 3 days' as Filter

                        RESIDENT Data1

                        WHERE Date>=Daystart(TODAY(),-3) and Date<today();

                         

                        CONCATENATE (FilterTable)

                        LOAD DISTINCT

                        Date,

                        'Last 30 days' as Filter

                        RESIDENT Data1

                        WHERE Date>=Daystart(TODAY(),-30) and Date<today();