count distinct and set analysis

[ninnartx]

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!!!

[Anil_Babu_Samineni · Accepted solution]

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))

[sunny_talwar]

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

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

[Chanty4u]

try this

small change in sunny exp

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

[sunny_talwar]

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

[ninnartx]

THANK YOU!!

[ninnartx]

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!