topic Re: Aggregating Values based on Max Date and Max Date -365 in QlikView

Aggregating Values based on Max Date and Max Date -365

Mon, 20 Apr 2015 20:29:21 GMT

I'm attempting to put together a financial statement that displays the current value based on the Max Date which is the quarter end date of 12/31/14. I am using the following expression that is giving me the correct values:

=Sum({$<[BS Date]={"$(=Date(Max([BS Date])))"}>}[Total Adjusted Fair Value])

I need to add a column that displays the values for the quarter end date of the prior year, 12/31/13, with no luck. Would adding -365 to the expression give me the prior year. The data is tagged with a 12/31/13 end date.

Re: Aggregating Values based on Max Date and Max Date -365

ramoncova06 — Mon, 20 Apr 2015 20:44:40 GMT

use addyears function

=Sum({$<[BS Date]={"$(=addyears(Date(Max([BS Date])),-1))"}>}[Total Adjusted Fair Value])

Re: Aggregating Values based on Max Date and Max Date -365

Mon, 20 Apr 2015 20:58:01 GMT

That worked. So if I wanted to make the label for the column dynamic what would be the expression. I'm currently using the following for the current year, 12/31/2014.

=max(([BS Date])) & 'Total Adjusted FV'

Re: Aggregating Values based on Max Date and Max Date -365

ramoncova06 — Mon, 20 Apr 2015 21:05:58 GMT

this should do it

addyears(max([BS Date]),-1)&' Total Adjusted XXX'