Data grouping

Hi

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).

Preparation

Accepted answers

NicoleJohnson

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.

Cheers!

all services-example.yxmd

All comments

SeanAdams

Hey @Kirstyp

Welcome to the community, and congratulations on your first post!

Are you able to provide some sample data to work through - that would really help to get you back on track as soon as possible?

Here's a reference that may help: https://community.alteryx.com/t5/Welcome-to-the-Alteryx-Community/Posting-for-fastest-possible-solution/td-p/69164

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.

Cheers

Sean

Kirstyp

Hi Sean

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.

Many thanks for your time and help.

NicoleJohnson

Cheers!

all services-example.yxmd

Kirstyp

Excellent, thanks again

Kirstyp

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177