Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Data grouping

Asteroid
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).
Highlighted
Nebula
Nebula

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

 

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

Highlighted
Asteroid
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.
Highlighted
Nebula
Nebula

Hey Kristy - I must apologise, but the attachment seems to have dropped off again - I've checked with both Chrome & Edge to be sure.

Would you mind checking if you can see your attachments on your two queries?

Highlighted
Asteroid
Hi

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.

thanks again
Highlighted
Asteroid

Hi Sean

 

I have tried to reattach? Any luck in receiving this? thanks again.

Highlighted
Nebula
Nebula
Highlighted
Magnetar
Magnetar

Hey @Kirstyp!

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!

 

Cheers,
NJ

Highlighted
Asteroid

Hi Nicola, here you go. 

Thanks :)

Highlighted
Magnetar
Magnetar

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!

NJ

Labels