Ignoring a filter with Aggr function

[m_m_fr]

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.

[sunny_talwar]

May be this

(Sum({<YEAR, WEEK>} Aggr(If(Only({<YEAR, WEEK>} [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]))

[m_m_fr · Accepted solution]

Thank you Sunny.

I have found the solution. You have to put the IF just after the SUM :

" (Sum({<WEEK=>} if (... "