Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted

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

Please also specify your expected result.

thanks

regards

Marco

Highlighted
Creator III
Creator III

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.

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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



Highlighted
MVP & Luminary
MVP & Luminary

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.