topic Re: Ignoring a filter with Aggr function in App Development

Ignoring a filter with Aggr function

m_m_fr — Thu, 27 Jul 2017 07:55:19 GMT

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.

Re: Ignoring a filter with Aggr function

sunny_talwar — Thu, 27 Jul 2017 15:35:41 GMT

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

Re: Ignoring a filter with Aggr function

m_m_fr — Tue, 01 Aug 2017 14:08:41 GMT

Thank you Sunny.

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

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