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:

       

      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.