4 Replies Latest reply: Nov 22, 2014 3:35 AM by Anand Chouhan

# Function in Set analysis

Hi all!

I have table like this:

ItemShop_idAmount
12345314
12345320
86367113
86367121

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

ItemAmount>0
1234532
8636712

• ###### Re: Function in Set analysis

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

• ###### Re: Function in Set analysis

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

• ###### Re: Function in Set analysis

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

• ###### Re: Function in Set analysis

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