Fastest way to get "active contracts" on a certain date

[chris_andersson]

Hi Qommunity,

I have a table which contains:

ContractID,

Contract startdate (Date field)

Contract enddate (Date field)

Now I want the users to be able to check how many contracts that is, or was, active on a given date. Active contract is a contract that have passed startdate, and not passed enddate.

I have made a generic date table that is not connected to any of the other tables, to be able to fetch the "vSelectionDate" and pass it on.

I have the following code in the chart "Active Contracts":

=Count(if([Contract startdate] >= '$(vSelectionDate)' and ([Contract enddate] >= '$(vSelectionDate)' or IsNull([Contract enddate])), ContractId))

This works, but it's very performance heavy. I don't get exactly how I could use flags in this case, since the selection date (E.g. historical dates) should be dynamic. If I understand it correctly, using flags in the loadscript will increase performance.

/Chris.

[MarcoWedel · Accepted solution]

maybe helpful:

Creating Reference Dates for Intervals

regards

Marco

[chris_andersson]

Thank you Marco!