17 Replies Latest reply: Apr 22, 2015 12:43 PM by Sunny Talwar

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

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.

Any help would be appreciated.

Best,

Sunny

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Unfortunately that just returned a value of 0

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

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

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

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?

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

May be try doing this in the script

SET DateFormat='MM/DD/YYYY';

YourTable:

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:

HTH

Best,

Sunny

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

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?

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

First Column Expression

Second Column Expression

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

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

Sunny

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

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

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Can you try this one for your second expression??

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

That did not work.

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Can you share a sample application to look at?

Best,

Sunny

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Sunny,

Let me play around with it a bit and see if I can get it to work.  Thanks for your assistance.

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Not a problem, I wish I was more helpful. Let me know if you don't find any way to fix it.

Best,

Sunny

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Thanks, you were very helpful.  Just need to play around with it a bit.

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Sunny,

Thanks, I was able to get it to work with a little bit of effort.  Your answer was correct.

• ###### Re: Sum Values using the addyears function with date format MM/DD/YYYY

Awesome! I am glad we were finally able to figure it out.

Best,

Sunny