Solved! Go to Solution.
Excellent, thanks again :)
Hi Nicole
Thanks again for this.
If I am trying to apply the same logic but to a 'as at' position (so open service at end of each month and end of each financial year) how do I tweak the multi row formula to capture this?
Sorry to ask direct, rather than re-post but as you worked on the original data I thought it was easier to ask you again re your workings.
Thanks.
Can you show me an example of what you'd expect it to look like? I'm not sure I completely understand what you're trying to achieve...
Yes,sorry , so within the data attached (I'm presuming you still have the workflow saved?) if I was to run this data today through the workflow and wanted to find out who was a current service user as at today date, I would look for all those clients who had a current service at today's date - the service end date is null or greater than today and the service start date is less than today (the workflow is looking at all activity of open and closed service users per year, month on month - which is great too I just need to split the data)
The 'as at' (current service user) at today date I would be looking for a unique client count of 3568
If I wanted to look for open at end of July - I would look for service user where
the service end date is null at the end of july
service end date is greater than the end of july
the service start date was after 30th July (as the service started after the month I was looking at)
End of June;
Service end is null at end of june
Service ended greater than end of june
Service started before end of june
and so on.
I would want to apply this month on month for each financial year and then also for each year end period, so as at 31st March 2014 there were xxxx clients with a current service, as at 31st March 2015 there were xxx etc etc
Sorry if I am not being very clear.
Ok I think I have a solution for you... see attached.
Top branch shows a simple method for determining the as at position for today's date using just a formula + filter + summarize tool.
Lower branch shows a method for determining the position by month, although it's not the most efficient calculation... basically figures out all possible months between the earliest start date and today, appends all those months to the data set, determines if the start date & end date requirements are met for the as at position for each month, filters for only those that were true, and then summarizes a count of distinct id's.
Let me know if that gets you the info you need!
Note that I am still seeing some "bad" dates in there for end dates, i.e. dates that are after today's date and in at least one case 2107 instead of 2017. :)
Hope this helps! :)
NJ