Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nelrhaffouli
New Contributor

Expression with condition on a list

Hello,

I would like to calculate the sum of number of calls for a list of account.

Let's say I have added a condition in the load manager to extract only a list of account.

I set it us as a flag "Y" if the account is in the list, "N" if it's not.

So I wanted to calculate the sum of number of calls for this list of account that got the condition "Y"

I used this

sum({$<[Central Account #]=P({1<Flag={‘Y’}>})>} [Nb of Cases])

But it gave me 0.

Do you have any idea what I did wrong?

Thank you so much for your help,

Tags (1)
8 Replies
micheledenardi
Valued Contributor

Re: Expression with condition on a list

You have forgot the dimension that P()  function has to returns.

So, your set analysis could be something like:

sum({$<[Central Account #]=P({1<Flag={‘Y’}>}[Central Account #])>} [Nb of Cases])



If you need other information on how to use P() or E() check the below links:

P() &amp; E() and where do you use them?

Set Analysis: syntaxes, examples

QlikView | Set Analysis | p(), e() and other aggregation function - YouTube

antoniotiman
Honored Contributor III

Re: Expression with condition on a list

Try this

sum({<Flag={‘Y’}>} [Nb of Cases])

nelrhaffouli
New Contributor

Re: Expression with condition on a list

Thank you Michel for your answer,

I used your correction, but it's still showing 0 :-/

I really don't know what went wrong

nelrhaffouli
New Contributor

Re: Expression with condition on a list

Thank you Antonio,

This is actually what I used before using this expression sum({$<[Central Account #]=P({1<Flag={‘Y’}>})>} [Nb of Cases])

But it was showing the total number of cases, like it was not taking into account my condition

micheledenardi
Valued Contributor

Re: Expression with condition on a list

Can yuo share some sample data and the expected result ?

nelrhaffouli
New Contributor

Re: Expression with condition on a list

Sure,

so basically, here is my flag in the data load editor

If([Next 1A Contract End]>=Date(Now()) and [Next 1A Contract End]<=Date(Now()+180),'Y','N') AS Flag,

basically it shows this dashboard

account list.png

so my objective is to display the number of calls generated by the number displayed 564 accounts.

is that clarifying a bit?

Thanks

micheledenardi
Valued Contributor

Re: Expression with condition on a list

Can you share some sample data in excel format or directly the .qvf ?

nelrhaffouli
New Contributor

Re: Expression with condition on a list

here you go

sample1.png

I couldn't attach the excel, this is basically a sample of list of account, and I want the number of calls for those accounts only

Do you need me to attach something else?

Thanks

Community Browser