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.