Aggregate function returning 0 values

[pkpandey]

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.

[jonathandienst]

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

[pkpandey]

Hi Jonathan

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

[jonathandienst]

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.