Multiple criteria in an expression

[zototo]

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!

[PrashantSangle]

Can you provide some sample data with required output?

[zototo]

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!

[StephenDunn]

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]

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]

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]

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

[zototo]

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

[zototo]

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)