This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am trying to group data to show year on year comparison of trend in activity of clients. I have trimmed by date of service start summarised by date/count of client then appended fields. I then have a filter to identify if service actual start is less than or equal to month start and service end is greater than month of start or null (this gives a as at position of client count with open services). When I run the data for this FY (April 2017 to date) this works, however when I input data from 2014 to date the as at position for 2017 changes to include a running count from 2014, e.g count at July 2017 should be 3000 when include data from previous years count is 7000. I want to be able to show the data at at each month for each financial year reflective of the FY, I know since 2014 we have had 8000 clients to date but year on year the total client count does not fluctate much (due to turnover each year).
it is possible to fill in data-gaps like you're referring to (we've used Generate Rows, or also joins in some cases) and when we can look at some sample data, it should be possible to get the solution you need.
In this example I have a large data set attached, I am trying to identify at each month within each financial year from 2014 to current date how many clients were supported through the period of the year and the position per month.
Also, how many of these clients were current at each month (as at figure) at least one of the clients service has no end date, during each financial year.
I have attached example data and also some screenshots of part of the workflow that isn't working.
Yes they were there, I have resent the attachments - there should be 3 attachments with this email, a word doc (data grouping per financial year) and a excel sheet (example) these relate to the data grouping query. The other (example - admissions to care) relates to the multi row query.
Can you please try attaching your actual workflow, not just the snippets in the Word doc? Will help so we don't have to try to guess at your formulas & configurations... you should be able to attach the workflow you've prepared so far the same way you were able to attach the Excel sample & Word doc, using the "Choose Files" options at the bottom of your response... once I can see the workflow you're starting with, I should be able to provide some suggestions for modifications that will give you the calcs you're looking for! Thanks!
Okay, so I was able to borrow pieces of the other workflow that we worked on to achieve what I think you're trying to do... essentially determining which Fiscal Year & Fiscal Month a client was active in, and then summarizing accordingly. Take a look at the attached, let me know if those numbers look in line with what you were expecting to see? Hope this helps! And I always like it when a segment of a workflow can be borrowed again... I'm all about reusing the stuff that works!
NOTE: While testing out some of my ideas, I discovered that you might have some "bad" data in there related to dates that are after today's date (later in 2017), and then in a couple cases it appears to be a case of transposition, as there were at least two records with ending dates in 2107 instead of 2017... so you might want to take a look at your data and run a few checks to make sure your dates are entered correctly in the original data in the first place. :)