2 Replies Latest reply: Aug 1, 2017 10:08 AM by M M RSS

    Ignoring a filter with Aggr function

    M M

      Dear Qlik Sense users,


      In order to implement a KPI, in the syntax I am trying to ignore the YEAR and WEEK filters with the Aggr function : whatever the filtre is selected, I always want to show the last month result of the selection.


      Example :

      • if my selection is YEAR 2015, I want to show the result of DECEMBER 2015.
      • if my selection is WEEK 2015-S33 (August 10th to 16th), I want to show the result  of AUGUST 2015.


      Here is the code :


           sum (

                       aggr (

                                    if ([Closing Date]-[Date of receipt]<18,

                                         count ({1<[Media]={'Email'},[Closing Date]={">=$(=monthstart(vDateMax)<$(=monthend(vDateMax))"}>} distinct [File Number])


                                    [File Number]






           count ({1<[Media]={'Email'},[Closing Date]={">=$(=monthstart(vDateMax)<$(=monthend(vDateMax))"}>} distinct [File Number]




      I have tried many solutions, I have looked all different possibilties in this website but nothing worked.


      Thank you.