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

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

Wed, 22 Apr 2015 15:23:37 GMT

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.

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

sunny_talwar — Wed, 22 Apr 2015 15:25:40 GMT

Try this instead:

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

Best,

Sunny

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

Wed, 22 Apr 2015 15:29:16 GMT

Unfortunately that just returned a value of 0

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

sunny_talwar — Wed, 22 Apr 2015 15:31:23 GMT

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

Wed, 22 Apr 2015 15:37:27 GMT

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

sunny_talwar — Wed, 22 Apr 2015 15:40:10 GMT

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

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

Wed, 22 Apr 2015 15:51:16 GMT

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

Wed, 22 Apr 2015 15:52:39 GMT

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])

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

sunny_talwar — Wed, 22 Apr 2015 15:54:50 GMT

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

Sunny

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

Wed, 22 Apr 2015 15:57:00 GMT

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

sunny_talwar — Wed, 22 Apr 2015 15:58:29 GMT

Can you try this one for your second expression??

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

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

Wed, 22 Apr 2015 16:00:29 GMT

That did not work.

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

sunny_talwar — Wed, 22 Apr 2015 16:01:41 GMT

Can you share a sample application to look at?

Best,

Sunny

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

Wed, 22 Apr 2015 16:03:55 GMT

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

sunny_talwar — Wed, 22 Apr 2015 16:08:34 GMT

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

Wed, 22 Apr 2015 16:09:41 GMT

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

Wed, 22 Apr 2015 16:18:16 GMT

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

sunny_talwar — Wed, 22 Apr 2015 16:43:59 GMT

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

Best,

Sunny