Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
What do you mean that it doesn't narrow the data set?
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)
How about if you multiple it by Avg(1)
Sum(WorkGrpMins) / Sum(TOTAL <UserId> {<cReportGroup=>} WorkGrpMins) * Avg(1)
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
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)
Sunny T, thank you sir. You hit the nail on the head.