topic Re: count distinct and set analysis in QlikView

count distinct and set analysis

ninnartx — Tue, 17 Oct 2017 04:58:53 GMT

Hi everyone,

I'm having an issue with count distinct and set analysis.

Basically each customer has a target score and an actual score.

I want to know how many customers have actual >= target AND target>0

so my formula is

Count(Distinct {<Customer={"=sum(Actual)>=sum(Target)"}*{"=sum(Target)>0"}>}Customer)

It works fine if my dimension = Customer.
However, if my dimension = Product, then the count is wrong.

I feel like the set analysis is incorrect... perhaps an aggr somewhere?

I can't seem to figure it out...

I've attached a qvw below.

Any help would be greatly appreciated!!!

Re: count distinct and set analysis

Anil_Babu_Samineni — Tue, 17 Oct 2017 05:26:50 GMT

Is this you are looking?

=Count({<Customer={"=(sum(Actual)>=sum(Target)) and (sum(Target)>0)"}>} DISTINCT Aggr(If((Sum(Actual) >= Sum(Target)) and Sum(Target)>0, Customer), Prod, Customer))

Re: count distinct and set analysis

sunny_talwar — Tue, 17 Oct 2017 12:05:00 GMT

May be you need this when you view th e chart by Prod

=Count(DISTINCT If(Actual >= Target and Target > 0, Customer))

Re: count distinct and set analysis

Chanty4u — Tue, 17 Oct 2017 12:30:25 GMT

try this

small change in sunny exp

=Count(If(Actual >= Target and Target > 0, DISTINCT Customer))

Re: count distinct and set analysis

sunny_talwar — Tue, 17 Oct 2017 12:33:16 GMT

I don't think DISTINCT can be used like you have used... I think mine was working just fine

Re: count distinct and set analysis

ninnartx — Thu, 19 Oct 2017 03:27:32 GMT

THANK YOU!!

Re: count distinct and set analysis

ninnartx — Thu, 19 Oct 2017 03:30:49 GMT

i got an error for both of the expressions above for some reason... but Anil's suggestion worked for me. Thank you so much anyway!