Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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.

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

Any help would be appreciated.

1 Solution

Accepted Solutions

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

Can you try this one for your second expression??

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

17 Replies

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

Try this instead:

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



Best,

Sunny

Not applicable

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

Not applicable

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:

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

Not applicable

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?

Not applicable

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

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

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

Sunny

Not applicable

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.

Community Browser