Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
zototo
Contributor III
Contributor III

Multiple criteria in an expression

Hi everyone, 

Hope to get some help.  What I need in the result is to have the COUNT of SHOP_NAME where both Game Suppliers "GF" and "PS" Count Game >0. 

I've tried different expressions but none of them is giving me the right result. Here's the latest one, but still it gives me the sum of shops where is only GF and only PS:

count({ $< COUNT_GAMES = {"=COUNT_GAMES > '0'"}, TermType = {'TB'}, [Game Supplier] = {'GF'}>+ <COUNT_GAMES = {"=COUNT_GAMES > '0'"}, TermType = {'TB'}, [Game Supplier] = {'PS'}>} distinct SHOP_NAME)

Would be great to have any ideas here.

Thank you!

Labels (3)
10 Replies
PrashantSangle

Can you provide some sample data with required output?

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
zototo
Contributor III
Contributor III
Author

Here's a sample. So what I expect as a result is a count of those shops, where TermType = "TB", and both {"GF" and "PS" } > 0.

In the data sample, there's no column TB, but it's already been considered under GF and PS columns.

Thank you!

Data Sample - Excel.jpg

StephenDunn
Contributor III
Contributor III

Hi,

This should work:

Count({$<COUNT_GAMES={">0"},TermType={'TB'},[Game Supplier]={'GF','PS'}>} DISTINCT SHOP_NAME)

You could also use:

Count({$<COUNT_GAMES-={0},TermType={'TB'},[Game Supplier]={'GF','PS'}>} DISTINCT SHOP_NAME)

( -={0}  means not equal to zero, which would still apply, assuming there is never a negative number of games…)

zototo
Contributor III
Contributor III
Author

Thank you for the idea, but this was the first thing I've tried and it does exactly the same, as my expression in the post: summaries shops with TermType TB and Game supplier GP and the same but with supplier GF. 

But I need to have ONLY those shops where both game suppliers' games played are > 0. 

StephenDunn
Contributor III
Contributor III

Hi Zototo,

Okay, you may need this then:

Count({$<COUNT_GAMES-={0}>*<TermType={'TB'}>*<[Game Supplier]={'GF','PS'}>} DISTINCT SHOP_NAME)

Unfortunately, the data you supplied doesn't really relate to the question you asked, so I just re-worked your original formula into a form that would work for the question.  Can you show a small sample of the data you're actually working with (where the column headers include 'COUNT_GAMES', 'TermType', and 'Game Supplier'), along with a summary of the expected result ?

 

 

zototo
Contributor III
Contributor III
Author

Still sum of both. I have a feeling that this part has to be somehow different. Is there any opportunity to use the operator AND in expression? 

[Game Supplier]={'GF','PS'}

 

PrashantSangle

Why don't you create flag in back end only. 

I suggest create that flag in backend and use that flag in your front end

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
zototo
Contributor III
Contributor III
Author

Hope this will help, as I have this data from at least 3 different tables. 

zototo
Contributor III
Contributor III
Author

Still, can anyone help me with AND and OR operators in expressions? Why are they working in Qlik View but not in Qlik Sense? 

Or is there smth wrong with my expression?

if([Game Supplier] = {'PS'} and [Game Supplier] = {'GF'} and TermType = {'TB'} and match(COUNT_GAMES = {"=COUNT_GAMES > '0'"})>0,count(distinct SHOP_NAME),0)