Skip to main content

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 
Search instead for 
Did you mean: 
pgonin
Contributor II
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.

Please help me solve this.

Thank you and have a wonderful weekend!

Labels (2)
1 Solution

Accepted Solutions
megglerock
Contributor II
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.

View solution in original post

2 Replies
megglerock
Contributor II
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.

Kushal_Chawda

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),