Agg function is not working in Single KPI

[mhmmd_srf]

Hi All,

I have one requirement like whenever we have Order Count  = 0, we will mention as 1. An then we will sum it up.

Expression is like:

if(sum({$<Z_DATE=,Holiday_HD ={'N'}, PLANT_HD = {'All'},M_DATE={">=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))<=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>} ORD_CNT)>0,'0','1')

above expression is working fine in Table. However if I put this in single KPI with Aggr function , the sum is coming one extra.

Sum(Aggr(if(sum({$<Z_DATE=,Holiday_HD ={'N'}, PLANT_HD = {'All'},M_DATE={">=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))<=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>} ORD_CNT)>0,'0','1'), M_DATE))

In the below screen shot count of 1 in BB column (total 12 dates have 1 in BB column) should be equal to the no display in Single KPI BB.

I am attaching my qvf file. Can any one please help me on this.

Please check My new sheet(1).

[mhmmd_srf]

Thank you so much Sunny... it is working fine.

Thanks a lot for being helpful always.

Sarif

[sunny_talwar]

No problem.... please close this thread by marking correct response if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny