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

Group by Month & Summarize

yashwanth522
7 - Meteor

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
5 REPLIES 5
jrgo
14 - Magnetar

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
Alteryx Alumni (Retired)

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

yashwanth522
7 - Meteor

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.

LukeG
Alteryx Alumni (Retired)

@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

yashwanth522
7 - Meteor

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

 

Thanks again for your time.

Labels