Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist
Specialist

Set Analysis Expression Help

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

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

Try this:

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

Hope this helps!

View solution in original post

4 Replies
maxgro
MVP
MVP

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
Specialist II
Specialist II

Try this:

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

Hope this helps!

View solution in original post

cbaqir
Specialist
Specialist
Author

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

jerem1234
Specialist II
Specialist II

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!