Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell474
Contributor 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

Re: Set Analysis Ignore Dimension – Filter Issue

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)

6 Replies

Re: Set Analysis Ignore Dimension – Filter Issue

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

jcampbell474
Contributor III

Re: Set Analysis Ignore Dimension – Filter Issue

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)

Re: Set Analysis Ignore Dimension – Filter Issue

How about if you multiple it by Avg(1)

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

jcampbell474
Contributor III

Re: Set Analysis Ignore Dimension – Filter Issue

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

Re: Set Analysis Ignore Dimension – Filter Issue

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
Contributor III

Re: Set Analysis Ignore Dimension – Filter Issue

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

Community Browser