topic Re: Date calculation help in QlikView

Date calculation help

alexis — Tue, 10 Feb 2015 02:35:26 GMT

I have the following formula:

= sum({$<[TransactionLinesDate] = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

vStartDate and vEndDate are typically the beginning of the month so we may have value 01/01/2015 (42005) and 01/02/2015 (42036).

I want to repeat the above calculation but for the month before - in other words instead of calculating for start of January until start of February, I want start of December 2014 to start of January 2015....

In other words I want vStartDate and vEndDate to be adjusted by one less month.

I have even tried the following and cannot get it to work...

= sum({$<[TransactionLinesDate] = {'>=$(=Date(vStartDate - 31))<=$(=Date(vEndDate - 31))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

Any help would be greatly appreciated...

Re: Date calculation help

anbu1984 — Tue, 10 Feb 2015 02:40:18 GMT

= sum({$<[TransactionLinesDate] = {'>=$(=AddMonths(Date#(vStartDate,'DD/MM/YYYY'),-1))<=$(=AddMonths(Date#(vEndDate,'DD/MM/YYYY'),-1))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

Re: Date calculation help

alexis — Tue, 10 Feb 2015 02:54:34 GMT

Thanks Anbu

I adapted what you suggested and got the desired result with the following - for some reason I was not getting the desired result with Date# and the formatting:

sum({$<[TransactionLinesDate] = {'>=$(=AddMonths(Date(vStartDate),-1))<=$(=AddMonths(Date(vEndDate),-1))'},

[TransactionLinesType] = {'I', 'C', 'T'}>}

TransactionLinesAmount)

Thanks

Alexis

Re: Date calculation help

anbu1984 — Tue, 10 Feb 2015 02:59:17 GMT

If the values stored in vStartDate and vEndDate are date, then you can directly use Addmonths() without Date()

Re: Date calculation help

alexis — Tue, 10 Feb 2015 09:33:07 GMT

vStartDate and vEndDate are numeric (e.g. 42005 for 01/01/2015) hence why I "Date" them and then deduct 1 month as you suggested.

Thanks again

Alexis