Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum 2 dates in the past

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

5 Replies
MarcoWedel

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

Please also specify your expected result.

thanks

regards

Marco

Anonymous
Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.