Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 😕
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
=SUM(Aggr(COUNT({<shop_id = {"=SUM(Amount)>0"}>}DISTINCT shop_id),item,shop_id))
=count(DISTINCT if(Amount>0, Shop_id))
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