Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
quriouss
Creator III
Creator III

Set Analysis with aggregated function?

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

4 Replies
sunny_talwar

Try this

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

quriouss
Creator III
Creator III
Author

That works a treat, thanks!

sunny_talwar

Done

wajiha
Contributor
Contributor

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