topic Re: Group by Month & Summarize in Alteryx Designer Desktop Discussions

Group by Month & Summarize

yashwanth522 — Mon, 21 Oct 2019 20:06:09 GMT

Hello All

This might be really simple but I'm unable to solve it,

I would like to group by Month and get a total count of Hrs for each Month. I was able to achieve it using a summarize tool but there is a small problem. As the total hrs are by Week. For the week starting on 9/30, the hours are calculated for September. But 9/30 week rolls into October as well. Can someone please help me out on how to group by and summarize the totals exactly for September and October?

For the week 9/30 we have Monday in September and Tuesday to Friday in October. I want Monday hours to be calculated in September and Tuesday to Friday hours in October.

I hope I'm making it clear. Let me know if that doesn't help.

I have the data similar to the following,

Date	Hrs	Month
2019-08-05 2019-08-12 2019-08-19 2019-09-02 2019-09-09 2019-09-16 2019-09-23 2019-09-30 2019-10-07	35.00 43.00 40.50 35.00 43.00 39.00 36.50 42.00 42.00	August August August September September September September September October

Re: Group by Month & Summarize

jrgo — Mon, 21 Oct 2019 20:23:26 GMT

Hi @yashwanth522

If the data set you receive only supplies you with total hours by week, I don't think you can. You need hours by day in order to get an accurate total by month.

If an estimate is possible, you can average the weekly total and split it evenly across all weekdays within that week then re-sum up everything, but again, it's not actuals.

Re: Group by Month & Summarize

LukeG — Mon, 21 Oct 2019 20:33:43 GMT

Hi @yashwanth522

One thing you can do, assuming that all hours from a week can be evenly spread across the days is use a generate rows tool to list each day and divide the total hours by 7 giving each day a value.

I've attached a workflow that completes this task. Let me know if this is along the lines of what you were thinking.

Luke

Re: Group by Month & Summarize

yashwanth522 — Tue, 22 Oct 2019 02:53:49 GMT

Thanks @LukeG for the quick response. But the login is failing few times,

BEGIN_DATE Sum_Hours Month New Date

Input (Full)

BEGIN_DATE Sum_Hours Month
2019-07-01 34.00 July
2019-07-08 37.50 July
2019-07-15 43.00 July
2019-07-22 37.00 July
2019-07-29 42.00 July
2019-08-05 35.00 August
2019-08-12 43.00 August
2019-08-19 40.50 August
2019-09-02 35.00 September
2019-09-09 43.00 September
2019-09-16 39.00 September
2019-09-23 36.50 September
2019-09-30 42.00 September
2019-10-07 42.00 October

Output (Truncated a bit)
2019-07-01 34.00 July 2019-07-01
2019-07-01 34.00 July 2019-07-02
2019-07-01 34.00 July 2019-07-03
2019-07-01 34.00 July 2019-07-04
2019-07-01 34.00 July 2019-07-05
2019-07-01 34.00 July 2019-07-06
2019-07-01 34.00 July 2019-07-07
2019-07-08 37.50 July 2019-07-08
2019-07-08 37.50 July 2019-07-09
2019-07-08 37.50 July 2019-07-10
2019-07-08 37.50 July 2019-07-11
2019-07-08 37.50 July 2019-07-12
2019-07-08 37.50 July 2019-07-13
2019-07-08 37.50 July 2019-07-14
2019-07-15 43.00 July 2019-07-15
2019-07-15 43.00 July 2019-07-16
2019-07-15 43.00 July 2019-07-17
2019-07-15 43.00 July 2019-07-18
2019-07-15 43.00 July 2019-07-19
2019-07-15 43.00 July 2019-07-20
2019-07-15 43.00 July 2019-07-21
2019-07-22 37.00 July 2019-07-22
2019-07-22 37.00 July 2019-07-23
2019-07-22 37.00 July 2019-07-24
2019-07-22 37.00 July 2019-07-25
2019-07-22 37.00 July 2019-07-26
2019-07-22 37.00 July 2019-07-27
2019-07-22 37.00 July 2019-07-28
2019-07-29 42.00 July 2019-07-29
2019-07-29 42.00 July 2019-07-30
2019-07-29 42.00 July 2019-07-31
2019-07-29 42.00 July 2019-08-01
2019-07-29 42.00 July 2019-08-02
2019-07-29 42.00 July 2019-08-03
2019-07-29 42.00 July 2019-08-04
2019-08-05 35.00 August 2019-08-05
2019-08-05 35.00 August 2019-08-06
2019-08-05 35.00 August 2019-08-07
2019-08-05 35.00 August 2019-08-08

On the output for 7/29, the new date is pointed to August! Which in turn results gives me inaccurate counts!! Can you please help? Let me know if you need more information.

Re: Group by Month & Summarize

LukeG — Tue, 22 Oct 2019 12:47:47 GMT

@yashwanth522 The only thing I can think of that would be causing this is the DateTimeFormat() formula using the previous date (2019-07-29) for each of the 7 records associated with that date.

If the formula is using the new date from the generate rows tool, it should show the accurate month.

In case I misunderstood the question, would you please clarify what you are referring to when you say 'inaccurate counts'?

Thanks,

Luke

Re: Group by Month & Summarize

yashwanth522 — Tue, 22 Oct 2019 20:45:18 GMT

Thanks @LukeG I was able to fix it. I didn't look at my results properly.

Thanks again for your time.