# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Contributor II

## counting values based on condition #2

Hello everyone!

I have a table like this (pairs sku+pharm_id are unique):

LOAD * INLINE [

sku, pharm_id,

sku1, a,

sku1, b,

sku1, c,

sku2, a,

sku2, b,

sku3, d,

sku3, e,

sku3, f

]

My original task is to calculate the amount of [pharm_id] in which all of selected [sku] appear (based on user input). Currently, there are no [pharm_id] which have the whole range of [sku] (sku1, sku2, sku3). But if user filters only sku1,sku2 then [pharm_id] a and b would have both of these [sku], then the answer in this case is 2. If user chooses only sku1, then the answer is 3 (all of a, b and c have sku1).

I tried smth like this

Count({<pharm_id = {"=count(pharm_id) = \$(distinct_sku) "}>} distinct pharm_id),

where variable distinct_sku, as you could guess, equals to count(distinct sku).

But, unfortunately it doesn't work as expected. While if I insert above instead of \$(distinct_sku) some number (e.g., 2),  it works perfectly.

Thank you and have a wonderful weekend!

Labels (2)

• ### Qlik Sense

1 Solution

Accepted Solutions
Contributor II

Does this do what you're after?

sum(if(aggr(count(sku),pharm_id)=GetPossibleCount(sku),1,0))

So if you select sku3 alone then this returns 3, but if you select it with either of the others it returns 0.

2 Replies
Contributor II

Does this do what you're after?

sum(if(aggr(count(sku),pharm_id)=GetPossibleCount(sku),1,0))

So if you select sku3 alone then this returns 3, but if you select it with either of the others it returns 0.

Expression works for me. May be you need to remove "=" from variable expression it should be just count( distinct pharm_id)

Count({<pharm_id = {"=count(pharm_id) = \$(distinct_sku) "}>} distinct pharm_id),

Tags
Community Browser