Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I made a measure for taking into account the efficiency of certain producers
(
Sum(
Aggr(
Count( {< ACTION_CODE = {'LABOR_OFF'}>} distinct MainSFC) * Standard_Time,
MainSFC)
)
)
/
(
Count(
distinct WorkingDay)
* 7.5 * 60)
And it works fine for the single producer. I want to abstract this, make one that takes into consideration ALL of the producers/employees, so in my mind I tried to do:
(
Sum( {<Employee=>}
Aggr(
Count( {< ACTION_CODE = {'LABOR_OFF'}, Employee=>}} distinct MainSFC) * Standard_Time,
MainSFC)
)
)
/
(
Count( {<Employee=>}
distinct WorkingDay)
* 7.5 * 60)
But this doesn't work, and gives me null values. How could I fix this?
(
Sum( {<Employee=>}
Aggr( {<Employee=>}
Count( {< ACTION_CODE = {'LABOR_OFF'}, Employee=>}} distinct MainSFC) * Standard_Time,
MainSFC)
)
)
/
(
Count( {<Employee=>}
distinct WorkingDay)
* 7.5 * 60)
Thank you @BrunPierre , it seems like this measure doesn't work, as it currently doesn't take any value anymore.
When you have a nested aggregation, you might need to use a set statement to ensure each part of the calculation considers the same or altered data selection.
Thank you @BrunPierre , apologies if I haven't expanded on the last answer. I tried to apply the same set of set analysis expressions to the formulae I was using.
I also tried a different, simpler approach were I just average the aggregate by date for the individuals and use set analysis to imply every individual, but I think this doesn't work because underlying I am still selecting an employee.
AvG({<Employee=>}
Aggr({<Employee= >}
//This measure is the one above in the post
[Individual Efficiency],
Date
)
)
Another approach worth considering. This expression only considers 'Dim1' and ignores all other dimensions.
=Avg({1<Dim1=$::Dim1>} Aggr(Sum({1<Dim1=$::Dim1>} Measure), Dim1, Dim2))
Like already hinted you may need to apply the appropriate selection state to each aggregation-part of your calculation, like:
sum({ Set } aggr({ Set } count({ Set } Field), Dim))
Beside this I think you may need aggr() to ensure that each part is calculated against the needed dimensionality, which might in your case:
sum(aggr(
sum(aggr(count() * Factor1, DimX)) /
sum(aggr(count() * Factor2, DimY)),
DimZ))
I suggest not to start with the entire calculation else developing it step by step by using several parallel expressions without any set analysis side by side and if it's returning the right results for a single respectively two selected values you could merge them within another expression and so on ...