Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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))
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))
May be you need this when you view th e chart by Prod
=Count(DISTINCT If(Actual >= Target and Target > 0, Customer))
try this
small change in sunny exp
=Count(If(Actual >= Target and Target > 0, DISTINCT Customer))
I don't think DISTINCT can be used like you have used... I think mine was working just fine
THANK YOU!!
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!