topic Re: Function in Set analysis in QlikView

Function in Set analysis

Fri, 21 Nov 2014 22:49:57 GMT

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. 😕

Re: Function in Set analysis

Fri, 21 Nov 2014 23:23:32 GMT

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

MK_QSL — Sat, 22 Nov 2014 07:33:34 GMT

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

Re: Function in Set analysis

anbu1984 — Sat, 22 Nov 2014 08:14:00 GMT

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

Re: Function in Set analysis

its_anandrjs — Sat, 22 Nov 2014 08:35:01 GMT

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