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

WIP Count by Month

ashleyinman
8 - Asteroid

I'm looking to solve a WIP count by month question. 

 

My data is updating daily and writing over previous repairs each day. When the unit is finally shipped, it will get a date in the "Ship_Date" column like below.

WIP Original.PNG

 

 

 

 

I would like to show for each month from the received date that the unit is not shipped out to count it as a WIP unit.
Something like below. I'm not sure if this is the best way to go about but would like to see if 

WIP.PNG

8 REPLIES 8
Joe_Mako
12 - Quasar

To Round a date up to the last day of the month, one route is use:
1. DateTimeTrim([Date],"month") to round down to the first of the month
2. DateTimeAdd([Date],1,"month") to add one month, making it the first of the next month
3. DateTimeAdd([Date],-1,"day") to subtract one day, resulting in the desired last day of the month

 

This applied in the attached workflow uses this approach:
- Generate Rows to add record for each relevant end of month date, "WIP Inventory"
- Formula to add "In WIP"

 

WIP.png

ashleyinman
8 - Asteroid

Awesome! Exactly what I was looking to do. I know the generate rows was needed but wasn't sure how to incorporate. Thanks! 

ashleyinman
8 - Asteroid

One question I just ran into with this and the generating row is how would you generate the row without ship dates? 

Joe_Mako
12 - Quasar

What would the input data look like, and what is your expected output? If could be as simple as using a Filter tool prior to the generate records to pull those record without a date out, and then a Union too after to combine the two streams.

ashleyinman
8 - Asteroid

With the Generate Row you provided, anything that has NO ship date gets removed. Is there an addition to the generate rows formula I can add to include the no ship dates as YES in WIP?

 

WIP NO SHIP.PNG

Joe_Mako
12 - Quasar

One option is to wrap the reference to [SHIP_DATE] in an IfNull() that will use Today's Date if it is Null. This edit is made in the attached.

 

 Edit, sorry IfNull() is not a built in function, post with fixed workbook in next reply

ashleyinman
8 - Asteroid

I'm receiving an IfNull Error on the generate rows.

 

NULL Error.PNG

Joe_Mako
12 - Quasar

here is the expression I added:

IF IsNull([SHIP_DATE]) THEN DateTimeToday() ELSE [SHIP_DATE] ENDIF
Labels