Group by Month & Summarize

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

Datasets

Developer

Parse

Date Time

Common Use Cases

Data Investigation

Time Series

Accepted answers

LukeG

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

Group by Month.yxmd

All comments

jrgo

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.

LukeG

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

Group by Month.yxmd

yashwanth522

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703