counting values based on condition #2

[pgonin]

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!

[megglerock · Accepted solution]

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