Function in Set analysis

[#1]

Hi all!

I have table like this:

Item	Shop_id	Amount
123453	1	4
123453	2	0
863671	1	3
863671	2	1

A need to show users how much shops have amount >0 for each item. It looks too simple, but i have some trouble to show it.

i use  in my table item as dimension and  expression like this:

count(distinct {<shop_id={"=sum(Amount)>0"}>} shop_id)

but it doesn't work, because for each my item it calculated both of this shops. This expression calculates total amount for each shop (ex. shop_id=2, amount=1 (1+0))

Item	Amount>0
123453	2
863671	2

Please see attach. Please help me with this set analysis. 😕

[#2]

Hi,

I use this formula:

if(sum(Amount)>0,

count({<Filter={'Stock Amount'}>} DISTINCT shop_id)

)

and works.

I hope it is that you need.

Regards

[MK_QSL]

=SUM(Aggr(COUNT({<shop_id = {"=SUM(Amount)>0"}>}DISTINCT shop_id),item,shop_id))

[anbu1984]

=count(DISTINCT if(Amount>0, Shop_id))

[its_anandrjs]

Hi,

Try this

count({< Amount = {"=sum({< Filter={'Stock Amount'}>}Amount)>0  "}>} DISTINCT shop_id)

If you requires for only > 0 values in place of shop_id use Amount

Regards

Anand