topic Re: Sum 2 dates in the past in QlikView

Sum 2 dates in the past

Tue, 01 Mar 2016 20:27:55 GMT

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

Re: Sum 2 dates in the past

MarcoWedel — Tue, 01 Mar 2016 21:34:57 GMT

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

Please also specify your expected result.

thanks

regards

Marco

Re: Sum 2 dates in the past

Anonymous — Tue, 01 Mar 2016 21:57:26 GMT

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.

Re: Sum 2 dates in the past

jagan — Wed, 02 Mar 2016 01:47:45 GMT

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.

Re: Sum 2 dates in the past

Wed, 02 Mar 2016 15:16:01 GMT

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

Re: Sum 2 dates in the past

jagan — Thu, 03 Mar 2016 00:29:25 GMT

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.