Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Data grouping

Kirstyp
8 - 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).
14 REPLIES 14
Kirstyp
8 - Asteroid

Excellent, thanks again :)

Kirstyp
8 - Asteroid

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.

NicoleJohnson
ACE Emeritus
ACE Emeritus

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

Kirstyp
8 - Asteroid

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. 

 

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

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

Labels