compare two fields with set analysis

[curiousfellow]

I have a table with employees and their sales per date.

every row has a field with their total sales and their goal.

I want to count the employees who reached their goal

so my expression =

count(distinct {<total_sales ={">= goal" }>}employee)

I tried much variations, with and without dollar-signs but no correct results.

What am I doing wrong ?

I tried to use a variable too, that compares both fields and result in a Yes or No (total_sales > goal)

but that did'nt work either

[swuehl]

If you want to make the comparison on a per record base, try

=Count(DISTINCT If( total_sales >= goal, employee))

[shraddha_g]

Try

count(distinct {<total_sales = {">= $(=sum(goal))" }>}employee)

[curiousfellow]

I have used this variation too, but I have to add more "analysis" conditions and then I am stuck

EDIT: placed the if statement now after the analysis after the "analysis" ....seems to work

[curiousfellow]

This does'n't give the correct result. It looks like if the comparison is made once and than the result is displayed for all employee

so when my expression is:  count(distinct {<total_sales = {">= $(=sum(goal))" }>}employee) then its allways 0 and when I  use count(distinct {<total_sales = {"<= $(=sum(goal))" }>}employee) then its allways 1

[sunny_talwar]

How about this may be:

Count(DISTINCT {<empolyee = {"=Sum(total_sales) >= goal"}>}employee)

[swuehl]

Johan Kruize wrote:

I have used this variation too, but I have to add more "analysis" conditions and then I am stuck

EDIT: placed the if statement now after the analysis after the "analysis" ....seems to work

That's what I would do:

Use Set Analysis for the filter that filters your dimensional values (not on a per record base) and the if() statement to filter on a per record base.

Or create a flag in your load script to compare goal and actual value on the required granularity and use the flag in your set analysis.