days and hours calculation

[Kirstyp]

Hi

I am trying to calculate the amount of hours provided by a provider within a time period.

I have attached a sample of data.

I want to calculate the average amount of hours provided (quantity) per week, then also per month and then per year.

I want to be able to sum the total hours also per client, per week, per month and then by provider if needed.  I am familiarising with summarising etc, I can't work out how to calculate my dates between into a week, month and year to average my quantity.

Thanks again :)

[Claje]

Hi,

Based on your sample data, and a few assumptions about its structure, I have a couple of suggestions:

First, I'd recommend converting your dates to Alteryx-supported date values.  You can do this via the DateTime tool in the Parse category, although you might need to normalize the "day" value (I encountered some conversion errors on days in the single digits, ie 5-May-17, which required me to add a leading 0.).  The methodology that i think is supported there is converting From d/-Mon,-yy.

From there, I made an assumption that if a record had no end date, we'd assume today's date.  I used a formula tool to update the EndDate field with the following formula:

IF ISEMPTY([EndDate]) THEN DATETIMETODAY() ELSE [EndDate] ENDIF

After that, I started looking at days/months.  I made an assumption that a month was 30 days and a week was 7 days, and that we always wanted to round these counts up - so a period that lasted 5 days would count as 1 week and 1 month.  This has some potential to overcount weeks/months, so there are some other options you could look at depending on exactly what period of time you are looking for.

I created two new fields in my formula tool, one for the count of weeks, and one for the count of months.  Their formulas are as follows:

//Weeks formula:
CEIL(DateTimeDiff([EndDate],[StartDate],'days')/7)
//Months Formula
CEIL(DATETIMEDIFF([EndDate],[StartDate],'days')/30)

After this, I used a summary tool to group by ID and Service level, and sum the Quantity, Weeks, and Months fields.

After that, one more formula tool gave me the final values, with simple division.

My final output looked like this

ID	Service Level	Sum_Quantity	Sum_Weeks	Sum_Months	HoursPerWeek	HoursPerMonth
1	Domicillary Care	2.25	35	9	0.064286	0.25
2	Domicillary Care	4.25	12	3	0.354167	1.416667
3	Domicillary Care	4.25	27	7	0.157407	0.607143
4	Domicillary Care	49.66	42	11	1.182381	4.514545
5	Domicillary Care	17	27	7	0.62963	2.428571
6	Domicillary Care	10.33	226	54	0.045708	0.191296
7	Domicillary Care	8.58	53	13	0.161887	0.66
8	Domicillary Care	68.75	69	16	0.996377	4.296875
9	Domicillary Care	1.75	30	7	0.058333	0.25

[Kirstyp]

Hi

Thanks so much for your quick reply.

Sorry I should have mentioned the quantity is  the weekly amount of hours delivered per week.  You are correct to presume if no end date then use today's date.  So I would be using the suggestions below but rather than dividing for the final outturn I would need to multiply?

Do you have any advice to calculate this on a monthly basis, per active week?

[Claje]

Ah-hah.  I figured that when I got to the fourth or fifth "i'm assuming..." in my post that I was probably getting something wrong!

At a high level, if you multiply the Quantity by the Sum_Weeks or Sum_Months value that will be somewhat close, but will not break the values out by month.

Based on the new question, I think you want to calculate a value for each month in a given period? EG for ID 1 you want a record for April, May, June,...,December?

If so, the Generate Rows tool is fantastic for this.  My preferred method is as follows:

Step 1, Generate Rows tool configured like so:

Then, after that you can use a formula tool, modifying EndDate with this formula:

IF DateTimeDiff(EndDate,StartDate,'days') >30 THEN DateTimeAdd(DATETIMEADD(StartDate,1,'months'),-1,'days') Else EndDate ENDIF
//This code checks if the final end date is more than 30 days away.  If it is, it creates a period that ends 1 day before the next month, otherwise it keeps the final end date

From there, you should have records split into monthly periods, which you can use to identify information about how many weeks there are in each month.  I think the methodology after this point depends pretty heavily on how you define a "week" - are you looking for Monday-Sunday time periods, counts of 7 days, or something else, and where are you allocating the remainder?  EG for 2016-04-13 through 2016-05-12, this period starts on a Wednesday, and ends on a Thursday.  How many weeks is that? 4, 5, or 4.14?

[Kirstyp]

Great, thanks.  I'm not great at the multi-row tool but I will have a go.

The week would be a standard 7 day week (Monday to Sunday), a 52.25 weeks in year

Thanks again

[AmeliaG]

I think this macro (The Date Aggregator) in the gallery would be a huge help to you! 

Here's the description in the gallery:

The Date Aggregator allows the user to roll up one or more fields that have been measured at the by-day level into a larger data interval (e.g. by week, month, quarter, etc.).

This tool will come in handy if you're interested in doing analysis on interval-over-interval (for example, week over week) changes across several different fields, or if you just feel like the data is currently too granular for meaningful analysis.

[Kirstyp]

Hi

Thanks for this.

I am wondering if there is something similar to do a count aggregation in addition to a sum?

I am trying to apply the same macro but to identify a count of deceased clients per week, month etc - when I use this it sums the ID of the client, rather than counting distinct.

[AmeliaG · Accepted solution]

Hi Kristy,

You can open up the macro and change the underlying logic to Count Distinct instead of Sum. To open the macro, right-click on the tool and select 'Open Macro'.

Best wishes,

Amelia