Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

Set Analysis Ignore Dimension – Filter Issue

I am using this expression:  sum(WorkGrpMins)  / sum(total <UserId> {<cReportGroup=>} WorkGrpMins) to ignore a dimension. The result is accurate, it just doesn't narrow the data set.

The dimension cReportGroup is in a listbox and when selected, the table with the expression doesn’t change to only the selected cReportGroup.  It keeps all of the records, but the set analysis expression result is accurate.  Looks like ignoring it using set analysis causes it to be ignored as a dimension too.

If I do that typical expression: sum(WorkGrpMins) / sum (total WorkGrpMins), then it's not aggregated by the UserId.  If I use sum(WorkGrpMins) / sum (total <UserId> WorkGrpMins) I get the numbers I need but they change to 100% when a cReportGroup is selected - hence ignoring the dimension in in the initial expression.

Does anyone know of a work-around to this?

1 Solution

Accepted Solutions
sunny_talwar

I guess the issue is that your expression for AvailMins in not zeroing out. May be you need to force it to be 0

If(Sum(WorkGrpMins) > 0, YourExpressionForAvailMins)

View solution in original post

6 Replies
sunny_talwar

What do you mean that it doesn't narrow the data set?

jcampbell474
Creator III
Creator III
Author

One, of many different ReportGroups is PPA.

I click on it and the all Reports groups change to 0% (%Time in Report Group) except PPA.  The % is accurate.  However, the table still shows all report groups.

When I select PPA, the (straight) table 'should' only show records in the PPA report group.  If I remove {<cReportGroup=>} from the Set Analysis and select PPA, the table will only show PPA records.  (But, everyone goes to %100)

sunny_talwar

How about if you multiple it by Avg(1)

Sum(WorkGrpMins)  / Sum(TOTAL <UserId> {<cReportGroup=>} WorkGrpMins) * Avg(1)

jcampbell474
Creator III
Creator III
Author

That formula works too - the % of time is accurate; however, I still have the same issue.  When I select the PPA group name (5th record down in the 2nd screenshot), the other groups do not disappear.  The expression result just changes to null ('-').

If this {<cReportGroup=>} is removed from the expression and PPA is selected, 4.55% will change to 100% and only the PPA record will show.

Before (PPA) Selection

before.PNG

After (PPA) Selection

After.PNG

sunny_talwar

I guess the issue is that your expression for AvailMins in not zeroing out. May be you need to force it to be 0

If(Sum(WorkGrpMins) > 0, YourExpressionForAvailMins)

jcampbell474
Creator III
Creator III
Author

Sunny T, thank you sir.  You hit the nail on the head.