Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
MVP
MVP

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