topic Re: Complicated date filters in QlikView

Complicated date filters

Wed, 25 Nov 2020 16:16:04 GMT

Lease_ID	Subspace_ID	Subspace Commence	Subspace Expire

436	500	11/20/2006	12/31/2015
436	638	1/1/2016	5/31/2017
436	639	6/1/2016	5/31/2017
436	640	6/1/2016	12/31/2026

I have 2 dates Commence date and Expire date.

From Subspace Expire exclude everything which is already expired and include only Subspace Expire date which falls within the rolling next 12 months.

From Subspace Commence date include everything less than today also include Subspace Commence date which falls within the rolling next 12 months.

So here I should get 638,639,640 as Subspace Coomence date falls in between rolling 12 months eventhough Subspace Expire for 640 doesnt fall between rolling next 12 months.Is it doable ?

Thank you.

Re: Complicated date filters

martinpohl — Thu, 24 Mar 2016 21:44:47 GMT

Hello,

so your where statement should be

where ([Subspace Expire] > today() //only in future

and {Subspace Expire] <= date(addyears(today(),1))) and //only until next 12 months

[Subspace Commence] <= date(addyears(today(),1)) //only dates less than next 12 months

addyears is by day, so from today (3/24/2016) addyears 1 gets 3/24/2017

so check if you need additionally monthstart or monthend

Regards

Re: Complicated date filters

swuehl — Thu, 24 Mar 2016 22:32:46 GMT

Or maybe

WHERE

NOT ( [Subspace Expire] < today() ) //Already expired excluded

AND

(

( [Subspace Expire] <= addyears(today(),1) ) //include

( [Subspace Commence] <= addyears(today(),1) ) // include

)

;