topic Re: Sum using AddYears Function for Date Formatted MMDDYYYY in QlikView

Sum using AddYears Function for Date Formatted MMDDYYYY

Mon, 27 Apr 2015 15:21:47 GMT

Hello,

I am having difficulties with the AddYears function just when I though I had figured it out and was working. I loaded my data setting the date format as follows:

SET DateFormat='MM/DD/YYYY';

I am trying to sum a value for a specific column based on the value for 1 year prior to the max year in the table using the following expression:

=Sum({$<[POS Date]={"$(=Num(AddYears(Date(Max([POS Date],'MMDDYYYY')),-1)))"}>}[POS Adjusted Fair Value])

At one point this was functioning, however when I reloaded my updated data something changed. Any help would be appreciated.

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

sunny_talwar — Mon, 27 Apr 2015 15:31:02 GMT

Try this (Assuming your [POS Date] is a number formatted field

=Sum({$<[POS Date]={"$(=Num(AddYears(Date(Max([POS Date])),-1)))"}>}[POS Adjusted Fair Value])

Best,

Sunny

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

Mon, 27 Apr 2015 15:33:10 GMT

Sunny,

It is actually formatted as a Date 'MMDDYYYY".

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

Mon, 27 Apr 2015 15:34:03 GMT

I updated your expression as follows and it worked:

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

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

sunny_talwar — Mon, 27 Apr 2015 15:39:52 GMT

I am glad it worked for you Jeff.

Best,

Sunny