community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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,

DateHrsMonth
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
Alteryx Certified Partner

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.

Alteryx
Alteryx

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.

 

Image.PNG

 

Luke

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.

Alteryx
Alteryx

@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

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

 

Thanks again for your time.

Labels