topic Re: Aggregate function returning 0 values in QlikView

Aggregate function returning 0 values

pkpandey — Fri, 23 Sep 2016 04:52:16 GMT

Hi All

I am to show two values one for the current YTD and another is for last year YTD. I am using the given expression for Current YTD

Sum(Aggr(

(fabs(Sum({<Year={$(=Max(Year))},DATE={'<=$(vMaxDate)'}>} PREPAY_AMOUNT))

Sum({<Year={$(=Max(Year))},DATE={'<=$(vMaxDate)'}>} PAY_AMT))*sum({<Year={$(=Max(Year))},DATE={'<=$(vMaxDate)'}>}distinct INR_RATE)

DATE,INVOICE_ID))/100000

and the following for the last year YTD

Sum(Aggr(

(fabs(Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PREPAY_AMOUNT))

Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PAY_AMT))*

sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>}distinct INR_RATE)

DATE,INVOICE_ID))/100000

Both the expressions are working fine when there is no selection. But when a date is selected the last year YTD is always showing 0.

I have tested the expressions individually and it returns values which is correct.

Any help will be highly appreciated.

Re: Aggregate function returning 0 values

jonathandienst — Fri, 23 Sep 2016 04:59:27 GMT

The Aggr() is being reduced by the selection, so you need to override the DATE selection:

Sum({<DATE>} Aggr(

(fabs(Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PREPAY_AMOUNT))

Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PAY_AMT))*

sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>}distinct INR_RATE)

DATE,INVOICE_ID))/100000

Re: Aggregate function returning 0 values

pkpandey — Fri, 23 Sep 2016 05:04:36 GMT

Hi Jonathan

Thank you for the quick reply, I have tried the expression but still getting the same result 0.

Re: Aggregate function returning 0 values

jonathandienst — Fri, 23 Sep 2016 05:09:35 GMT

Note that you must include all the selections you need to override. Are you selecting DATE, and are you selecting other fields. For example, if you are selecting Month and Year:

Sum({<Month, Year>} Aggr(

(fabs(Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PREPAY_AMOUNT))

Sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>} PAY_AMT))*

sum({<Year={'$(vMaxYearLY)'},DATE={'<=$(vMaxDateLY)'}>}distinct INR_RATE)

DATE,INVOICE_ID))/100000

Otherwise, I suggest you upload a small sample with some representative data for more detailed check.