Set Analysis Expression Help

[cbaqir]

There has got to be an easy answer to this but after trying everything I could think of for the last week, I am asking for help from the esteemed Qlik Community....

The attached QVW is showing timesheet compliance but there is something wrong in my compliance % calculations when the Y/N option is selected on Submission Compliance or Approval Compliance.

If SUBMIT_COMP = Y is selected in the list box, Submission Compliance shows 100% which makes sense.

However...

If SUBMIT_COMP = N is selected in the list box, I would expect Submission Compliance to show 0% but it shows 28.6%.

Submission Compliance =

=num(Count(DISTINCT{$<[SUBMIT_COMP]= {'Y'}>} [TIMESHEET_LINE_ID])/((count(distinct {$}[TIMESHEET_LINE_ID]))), '##.0%')

What on earth am I missing?

Thanks,

Cassandra

[maxgro]

make 2 textbox with 2 expression

1)      =count(DISTINCT{$<[SUBMIT_COMP]= {'Y'}>} [TIMESHEET_LINE_ID])

2)      =count(distinct {$}[TIMESHEET_LINE_ID])

                                                                         1)    2) 

when you don't select SUBMIT_COMP           2     9               Y / ALL

when select N                                                  2     7               Y / N

when select Y                                                  2     2                Y / Y

[jerem1234 · Accepted solution]

Try this:

=num(Count(DISTINCT{$*<[SUBMIT_COMP]= {'Y'}>} [TIMESHEET_LINE_ID])/((count(distinct {$}[TIMESHEET_LINE_ID]))), '##.0%')

Hope this helps!

[cbaqir]

Thanks. For my own knowledge, what does adding the * do?

[jerem1234]

It does an intersection between the default state and your set analysis. The asterisk is the symbol for intersection in set analysis. The reason to do this is because when you are specifying the field in set analysis, then select on it, your expression does not filter that field since you explicitly say in your set analysis:

[SUBMIT_COMP]= {'Y'}

So Count(DISTINCT{$<[SUBMIT_COMP]= {'Y'}>} [TIMESHEET_LINE_ID]) will show 4 without selection for example, then when you select on [SUBMIT_COMP] and choose 'N', it will still say 4. It won't change and will still count all your Y's. So to fix this, just intersect the sets of the default state ($) and your set analysis. This way, it grabs everything from your selections, but then only retrieves the Y's from your selection. That way it will give back 0 instead of 4.

Hope this helps!