Sum Values using the addyears function with date format MM/DD/YYYY

[#1]

I am attempting to sum a value of a column of data using the date with a format of MM/DD/YYYY.  I am using the following expression and having no luck.

=Sum({$<[POS Date]={"$(=addyears(Date(Max([POS Date]),'MM/DD/YYYY'),-1))"}>}[POS Adjusted Fair Value])

Any help would be appreciated.

[sunny_talwar]

Try this instead:

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

Best,

Sunny

[#3]

Unfortunately that just returned a value of 0

[sunny_talwar]

The date format for [POS Date] is 'MM/DD/YYYY', right? If it is not than your expression will give you 0. Can you check the format of your [POS Date]

Best,

Sunny

[#5]

Could it be when I load the data from the spreadsheet that it is being loaded as a numeric value instead of a date?  The Number 42004 keeps popping us in the date field.  Would reloading the data and using the following:

SET DateFormat='MM/DD/YYYY';

Fix the issue?

[sunny_talwar]

May be try doing this in the script

SET DateFormat='MM/DD/YYYY';

YourTable:

LOAD YourFields,

          Date([POS Date]) as [POS Date]

FROM xyz;

and then use this =Sum({$<[POS Date]={"$(=Date(AddYears(Max([POS Date]), -1), 'MM/DD/YYYY'))"}>}[POS Adjusted Fair Value])

Alternatively you can try this as your expression:

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

HTH

Best,

Sunny

[#7]

I'm seeing values, but I want the first column of data to display the sum for POS Adjusted Fair Value when the date is equal to 12/31/2014 and the second column to show the sum for the previous Year's quarter (date = 12/31/2013).  Make sense?

[#8]

First Column Expression

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

Second Column Expression

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

[sunny_talwar]

Are you seeing value for your first column right now???

Sunny

[#10]

Yes, I see the same values for the first column that I see in the second column.