Aggregation from different tables and create a dimension
Hi there, I have created the measure below and it works fine; from that, I need to create dimensions. The columns come from different tables. I appreciate your help on this.
Below formula giving me the correct values.
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI
However, when I use the above formula to create dimension as show below, it does not show up, even though it does not give me any error.
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>0 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<60, 'Good',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>61 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<150, 'Fair',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>151 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<240, 'Bad',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>241 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<300, 'Very Bad'))))
It should return an error because you are using aggregation-functions without applying an aggr() wrapping to specify the dimensional context for the calculations. Further possible errors may be a timeout or a ran out of memory message - because this kind of calculation could become very heavy especially if the parts come from different tables and/or aren't associated suitable.
Beside this you are using +- operators between the operands. This will only be working if all parts return always a valid number - if not the entire calculation will become NULL. This could be fetched with a range-function and in the end your expression may go in this direction:
It should return an error because you are using aggregation-functions without applying an aggr() wrapping to specify the dimensional context for the calculations. Further possible errors may be a timeout or a ran out of memory message - because this kind of calculation could become very heavy especially if the parts come from different tables and/or aren't associated suitable.
Beside this you are using +- operators between the operands. This will only be working if all parts return always a valid number - if not the entire calculation will become NULL. This could be fetched with a range-function and in the end your expression may go in this direction:
