Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
I have an expression which works perfectly when i have the Week No as the dimension in a Line Chart or a Table. But i want this expression to be calculated by Aggregating over the week and give me a Count. How can i do that?
Here is the expression:
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))=0
and
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))=0
and
$(vUpperLimit)=0,0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))<>0
and
$(vUpperLimit)=0,
if(Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))=0,0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue))))),1,0)),
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))<>0
and
$(vUpperLimit)<>0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))
OR
Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=$(vUpperLimit),1,0))))
Any help is highly appreciated.
Best Regards
Sunil
Perhaps this?
Sum(Aggr(if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))=0
and
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))=0
and
$(vUpperLimit)=0,0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))<>0
and
$(vUpperLimit)=0,
if(Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))=0,0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue))))),1,0)),
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))<>0
and
$(vUpperLimit)<>0,
if(Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=
Ceil(RangeAvg(above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))+
($(vyValue)*RangeStdev(Above(sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]),0,$(vnValue)))))
OR
Ceil(Sum({<[KPI Name]={'$(=($(vChartKPI)))'},[Week Name]={"<=$(=Max([Week Name]))"}>}[Week's KPI Value]))>=$(vUpperLimit),1,0)))), Week))