2 Replies Latest reply: Dec 8, 2017 1:38 AM by Christoffer Andersson RSS

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

    Christoffer Andersson

      Hi Qommunity,


      I have a table which contains:



      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.