Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Sum using AddYears Function for Date Formatted MMDDYYYY

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.

1 Solution

Accepted Solutions
Not applicable

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

I updated your expression as follows and it worked:

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

4 Replies

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

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

Not applicable

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

Sunny,

It is actually formatted as a Date 'MMDDYYYY".

Not applicable

Re: Sum using AddYears Function for Date Formatted MMDDYYYY

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

I am glad it worked for you Jeff.

Best,

Sunny

Community Browser