I am trying to user firstsortedvalue function in a KPI object.
I would like to see in the KPI the day-hour with the highest sum(kpi) value. In the example below will be "tuesday-3" (the sum(kpi) is 120.
I tried FirstSortedValue(day & '-' & hour,sum(kpi)) but got a "nested aggregation not allowed error" and then I tried FirstSortedValue(day & '-' & hour,-aggr(sum(kpi),day & '-' & hour)) without success.
Does anyone can help me?
Thanks in advance
load * inline
[
day,hour,kpi
monday,1,10
monday,1,10
monday,1,10
monday,2,11
monday,2,11
monday,2,11
tuesday,1,5
tuesday,2,5
tuesday,3,40
tuesday,3,40
tuesday,3,40
wednesday,1,5
wednesday,2,5
wednesday,3,5
];
