Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
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.