topic Re: Agg function is not working in Single KPI in QlikView

Agg function is not working in Single KPI

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Agg function is not working in Single KPI

Anonymous — Thu, 26 Oct 2017 16:23:32 GMT

try

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

Re: Agg function is not working in Single KPI

sunny_talwar — Thu, 26 Oct 2017 16:54:18 GMT

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

Re: Agg function is not working in Single KPI

mhmmd_srf — Thu, 26 Oct 2017 17:50:24 GMT

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

Re: Agg function is not working in Single KPI

sunny_talwar — Thu, 26 Oct 2017 17:58:37 GMT

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

Re: Agg function is not working in Single KPI

sunny_talwar — Thu, 26 Oct 2017 18:01:00 GMT

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(

, M_DATE))

Re: Agg function is not working in Single KPI

mhmmd_srf — Thu, 26 Oct 2017 18:05:09 GMT

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

Re: Agg function is not working in Single KPI

sunny_talwar — Thu, 26 Oct 2017 18:09:06 GMT

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

Re: Agg function is not working in Single KPI

mhmmd_srf — Thu, 26 Oct 2017 18:14:33 GMT

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

Re: Agg function is not working in Single KPI

sunny_talwar — Thu, 26 Oct 2017 18:30:38 GMT

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)

Re: Agg function is not working in Single KPI

mhmmd_srf — Tue, 31 Oct 2017 09:21:47 GMT

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

Thanks a lot for being helpful always.

Sarif

Re: Agg function is not working in Single KPI

sunny_talwar — Tue, 31 Oct 2017 11:12:55 GMT

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