topic Re: Set Analysis with aggregated function? in QlikView

Set Analysis with aggregated function?

quriouss — Fri, 02 Jun 2017 13:04:47 GMT

I have a table of customers who have bought things, and some of whom have returned things for a refund. I want to count the number of customers who have a net positive transaction value;

Row Number Customer Transaction Value

1 CustomerA $1000

2 CustomerA -$1000

3 Customer B $500

Should give the answer "One Customer"

Count(Distinct {<[Transaction Value] = {">0"}>} [Customer] ) will give the answer "2" because it will count Row 1 & Row 3.

I have tried Count(Distinct {<Aggr (Sum ([Transaction Value])) = {">0"}>} [Customer] ) but that gives an error in the formula editor.

I also tried, Count(Distinct {<'Sum ([Transaction Value]) >0'>} [Customer] ) which is accepted, but gives the wrong answer (I think it just counts all records

Any clues???

Thanks.

Re: Set Analysis with aggregated function?

sunny_talwar — Fri, 02 Jun 2017 13:12:37 GMT

Try this

Count(DISTINCT {<Customer = {"=Sum([Transaction Value]) >0"}>} Customer)

Re: Set Analysis with aggregated function?

quriouss — Fri, 02 Jun 2017 13:21:44 GMT

That works a treat, thanks!

Re: Set Analysis with aggregated function?

sunny_talwar — Fri, 02 Jun 2017 13:23:09 GMT

Done

Re: Set Analysis with aggregated function?

wajiha — Thu, 21 Nov 2019 08:28:08 GMT

what if i want a range here e.g my expression is: Count(DISTINCT {<[KYSC.FLOAT_DAY] = {"=AVG([KYSC.FLOAT_DAY])>=0"}>} [KYSC_SALE_CHNL_CD])

also does it work with avg?

my range is AVG([KYSC.FLOAT_DAY])>=0<1.

thanks.

Wajiha