topic Re: Set Analysis Ignore Dimension – Filter Issue in QlikView

Set Analysis Ignore Dimension – Filter Issue

jcampbell474 — Tue, 15 Nov 2016 19:47:38 GMT

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?

Re: Set Analysis Ignore Dimension – Filter Issue

sunny_talwar — Tue, 15 Nov 2016 20:05:59 GMT

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

Re: Set Analysis Ignore Dimension – Filter Issue

jcampbell474 — Tue, 15 Nov 2016 20:12:20 GMT

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

sunny_talwar — Tue, 15 Nov 2016 20:16:31 GMT

How about if you multiple it by Avg(1)

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

Re: Set Analysis Ignore Dimension – Filter Issue

jcampbell474 — Tue, 15 Nov 2016 20:26:58 GMT

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

After (PPA) Selection

Re: Set Analysis Ignore Dimension – Filter Issue

sunny_talwar — Tue, 15 Nov 2016 20:29:44 GMT

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)

Re: Set Analysis Ignore Dimension – Filter Issue

jcampbell474 — Tue, 15 Nov 2016 20:41:18 GMT

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