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

[Anonymous]

try

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

Otherwise why not just use a variable or

sum( TOTAL 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'))

[sunny_talwar]

May be this

Count({$<Z_DATE=,Holiday_HD ={'N'}, PLANT_HD = {'All'},M_DATE={">=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))<=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>}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, Null(), Date(M_DATE))

, M_DATE))

[mhmmd_srf]

Hello Sunny,

Seems it is working. Let me test with few more cases.

But what was the issue with my expression?

It would be really helpful, you can help me to understand.

Thanks,

Sarif

[sunny_talwar]

See here what I get when I use this expression

Concat({$<Z_DATE=,Holiday_HD ={'N'}, PLANT_HD = {'All'},M_DATE={">=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 90)))<=$(=(Date(Date#('$(vDATE)','M/D/YYYY') - 1)))"}>}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, Null(), '1-'&Date(M_DATE))

, M_DATE), ', ')

Notive the first 1-, which gets summed when you used Sum.... not sure why, but it was summing one addition M_DATE... May be it is null or something else. Don't completely understand it. But counting M_DATE resolves the issue

[sunny_talwar]

Alternatively, this also worked

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)))"}>}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 or Len(Trim(M_DATE)) = 0, 0, 1)

, M_DATE))

[mhmmd_srf]

Hello Sunny,

But when I changed the Measure from ORD_CNT to DLV_HDR_CNT, now it is showing one less.

It should come 25 , but it is coming as 24.

Actually my expression was perfect with DLV_HDR_CNT. My expression is returning 25.

My expression was not working for ORD_CNT.

I am attaching this again. Could you please check once.

I have already spent two days. Did not get this thing.

Please help.

Thanks,

Sarif

[sunny_talwar]

I exported your chart and the count I got was 24... why do you want a count of 25 here?

[mhmmd_srf]

Oh.. my bad... extremely sorry...

so which expression do u suggest. Both r working.

Let me test with few more scenario.

Thanks a lot Sunny. You are always helpful.

Thanks,

Sarif

[sunny_talwar]

I propose using this expression

=Count(DISTINCT{$<M_DATE = {"=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)))""}>} DLV_HDR_CNT)<=0"}>} M_DATE)