Filter the dimension based on the value of the expression

[leenlart]

Hi,

I feel like I should have found something like this in the discussions, but...

So I have a table where I want to show the top 20 new clients. 

The dimension is the client.

The expressions are volume N and volume N-1 and others. 

I'd like to show only the clients that have volume N >0 and volume N-1=0.  (Definition of new clients)

Then I'll apply my limit to only show the top 20.

I've tried putting the following condition on my client dimension and suppressing null values :

IF(

(Sum({<TYPE_CALENDRIER={'YTD CAL'}>}fp_poids)>0 and Sum({<TYPE_CALENDRIER={'YTD CAL N-1'}>}fp_poids)>0)

,

fp_ClientComplet

,

Null()

)

But it does not seem to affect anything. 

Thanks for any ideas you might have.

[sunny_talwar · Accepted solution]

Try adding Aggr() function and see if that works

Aggr(If(

(Sum({<TYPE_CALENDRIER = {'YTD CAL'}>} fp_poids) > 0 and Sum({<TYPE_CALENDRIER = {'YTD CAL N-1'}>} fp_poids) > 0), fp_ClientComplet, Null()), fp_ClientComplet)

[Anil_Babu_Samineni]

From this condition, You want to show top 20 Clients? Perhaps this as per my analysis

IF((Aggr(Sum({<TYPE_CALENDRIER={'YTD CAL'}>}fp_poids), client)>0 and Aggr(Sum({<TYPE_CALENDRIER={'YTD CAL N-1'}>}fp_poids), client)>0),fp_ClientComplet,Null())

[leenlart]

So initially I was trying to put this expression into the condition of my dimension, but instead I created a calculated dimension. 

Both expressions seems to return the same results. 

Thanks very much for your help!

[sunny_talwar]

Super, please close the thread by marking correct and helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny