Sum 2 dates in the past

[#1]

Hello,

I am trying to sum a value between two dates but I would like to do it in the "past" meaning I would like to subtract 1 year from the date range once I've selected it.

Here is what I have tried so far but it is not working

Sum({$<[Invoice Header.Order Creation Date] = {'>=$(=Year(Min([Invoice Header.Order Creation Date]))-1)<=$(=Year(Max([Date]))-1)'}>}[Invoice Line.Amount Including VAT])

Invoice creation date is the start and "date" is what we call our "posting date" or the date on which the invoice was finalized. I need to sum the values between the start and end of the invoice life but for 1 year ago.

Can anyone help?

Thanks

[MarcoWedel]

please post a sample application including some test data to work with.

Please also specify your expected result.

thanks

regards

Marco

[Anonymous]

If I were to guess, Qlikview isn't interpreting your date field as an integer.   You can include Date# in the Set Analysis or  create a Year Field in your Data Model to make the Set Analysis a little more readable.

[jagan · Accepted solution]

Hi,

I am not sure why you are using Year in the expression?  It just gives 2014, 2015, 2016,....  Is your date field [Invoice Header.Order Creation Date] consists of dates or years?

If dates then use this

=Sum({$<[Invoice Header.Order Creation Date] = {'>=$(=YearStart(Min([Invoice Header.Order Creation Date]),-1))<=$(=YearEnd(Max([Date]),-1))'}>}[Invoice Line.Amount Including VAT])

Hope this helps you.

Regards,

jagan.

[#5]

in both case the fields are date fields

MM/DD/YYYY

[Invoice Header.Order Creation Date] = 1/20/2016

[Date] = 2/15/2016

I want to take the range between these 2 dates and move them back exactly 1 year so the dates would be

[Invoice Header.Order Creation Date] = 1/20/2015

[Date] = 2/15/2015

Then I want to sum any records that fall within the new dates

[jagan]

Try this using AddYears() for this

=Sum({$<[Invoice Header.Order Creation Date] = {'>=$(=AddYears(Min([Invoice Header.Order Creation Date]),-1))<=$(=AddYears(Max([Date]),-1))'}>}[Invoice Line.Amount Including VAT])

Hope this helps you.

Regards,

jagan.