I want to make a "active object" calculation, which I can put in summary layouts, and, especially, track the proportion of how many active objects turn to sold on the given period.
I can share a filter portal formula, where I achieved this. Layout is based on ACTIONS table, portal is based on OBJECTS_Actions, connection link is: ACTIONS::account_create = OBJECTS_Actions::account create. On that layout I use ACTIONS::xdate global field and then run a triggered script to find only actions from that date.
Formula that I use in the filter portal (for that part) is:
(IsEmpty (OBJECTS_Actions::date_terminate) xor OBJECTS_Actions::date_terminate > ACTIONS::xdate) and
(IsEmpty (OBJECTS_Actions::date_preliminary) xor OBJECTS_Actions::date_preliminary > ACTIONS::xdate) and
(IsEmpty (OBJECTS_Actions::date_sale) xor OBJECTS_Actions::date_sale > ACTIONS::xdate)
My subsummary layout is also based on ACTIONS table, but there all actions with all potential prospects and customers are stored. And there ACTIONS::a_day field is used in all subsummary calculations and sorting.
I have subsummaries by year, half, quarter, month, week and day. The rule is, if by the end of each period Object becomes inactive (i.e. contract is terminated, or preliminary/notary agreements are signed), it shouldn't be displayed as Active in that period's summary. Like, if the subsummary is by month, and I see the month of July, 2018, but object went under preliminary agreement, it shouldn't be displayed in July's subsummary, but, if it was Active by the end of June - it should be included into June's subsummary.
So how do I achieve this?