Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

marouane_magtou
New Contributor III

Ignoring a filter with Aggr function

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.

1 Solution

Accepted Solutions
marouane_magtou
New Contributor III

Re: Ignoring a filter with Aggr function

Thank you Sunny.

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

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

2 Replies

Re: Ignoring a filter with Aggr function

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]))

marouane_magtou
New Contributor III

Re: Ignoring a filter with Aggr function

Thank you Sunny.

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

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

Community Browser