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

YTD calculation

harsha384
8 - Asteroid

Hi All,

 

I need some help for YTD calculation. The condition should be that if the year doesn't have January month data, the YTD of that year should be zero.

Please find the attached file. The required YTD output is calculated in the sheet.

 

Thanks

Harsh

3 REPLIES 3
Joe_Mako
12 - Quasar

How about the attached?

 

YTD.png

- Filter to remove the blank lines from your example file, may not be necessary with your real data if there are no blank records

- Multi-Field Formula to convert the Date field's data type, may not be necessary with your real data if it is already a data type

- Formula to pull out the Year from the Date

- Summarize to group by Year and return the Min Date

- Formula to convert the Min Date in the Min Month as a number

- Join by Year so every record has the Min Month number for its Year

- Sort by Date to ensure the correct sort

- Multi-Row Formula to perform a running sum if the Min Month is 1, zero if it is not 1, and it is set to Group by Year so it restarts the running sum for each Year

jdunkerley79
ACE Emeritus
ACE Emeritus

I like @Joe_Mako solution but here is a variant approach.

 

- Use a Running Total tool to make the sum of the month as we go along (makes assumption that Date is ordered, add a sort if not)

- Use a formula tool to make up the Jan date (01-01-<Year>)

- Use a find and replace to self join on the new field back to the old (Find and Replace doesnt change input order and does a left outer join :))

- Finally use a formula tool to make YTD

 

2017-04-28_16-51-28.jpg

harsha384
8 - Asteroid

Thanks Joe. This solution works for me.

Labels