1 of 1 people found this helpful
Can you attach packaged workbook (twbx), then the people in community can help you more easily.
Here you go! Keep in mind this is all dummy data, and due to the nature of how it was generated the only timestamps I would trust are policy_eff_date which currently shows the date the message was effective. Date is set up to show either that or 6 months from that based on the data source. Unit is the full source and the other 6 are copies with event_type filtered. The other timestamps were randomly generated by the consultants who made the test data (we're not fully in production at the moment)
The two sheets are the different attempts I've had at graphing this up to now.
Book4.twbx 133.9 KB
I found the solution. I'll post my workbook for anyone who has this question in the future.
The relevant Calculated fields are:
Event Date (a collection of various date fields (one from each source)
Start (calculated over Day of Event Date then Policy ID)
running (Calculated over Policy ID and then Day of Event Date and restarting every Policy ID)
isActive (Calculated over Event Date)
isCancelled (Calculated over Event Date) (probably could have been one field with isActive)
NumActive (Calculated over Policy ID)
The different sources are all duplicates of dcbi_dev with filters applied to each one so they only see one event type and one date type.
Solution.twbx 174.5 KB