topic Re: compare two fields with set analysis in QlikView

compare two fields with set analysis

curiousfellow — Tue, 25 Oct 2016 09:08:14 GMT

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

Re: compare two fields with set analysis

swuehl — Tue, 25 Oct 2016 09:12:45 GMT

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

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

Re: compare two fields with set analysis

shraddha_g — Tue, 25 Oct 2016 09:15:05 GMT

Try

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

Re: compare two fields with set analysis

curiousfellow — Tue, 25 Oct 2016 09:33:38 GMT

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

Re: compare two fields with set analysis

curiousfellow — Tue, 25 Oct 2016 09:48:25 GMT

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

Re: compare two fields with set analysis

sunny_talwar — Tue, 25 Oct 2016 09:59:42 GMT

How about this may be:

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

Re: compare two fields with set analysis

swuehl — Tue, 25 Oct 2016 10:34:39 GMT

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.