How to aggregate my expression over the Week No

[qliksunil]

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

[saminea_ANZ]

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))

[qliksunil]

I tried this but it is not working. As there are lot of if clauses in between. it is not working