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 - Calculate YTD Amount based on month name

david_shishir
6 - Meteoroid

Hi All,

 

I have data by month as shown in the below screenshot. I need alteryx to calculate YTD based on the months completed in an year. Also the last completed month should be current month. for example if we are in Dec, current month will be Nov and YTD will be Jan-Nov. 

 

Kindly need your help.

 

david_shishir_0-1672154237053.png

 

Thanks,

David

7 REPLIES 7
ShankerV
17 - Castor

Hi @david_shishir 

 

Yes, it is possible.

 

You can get the current month with help of datetimenow() and then perform the calculation for the previous months.

 

Let me know if you need to build a solution for the same.

 

Many thanks

Shanker V

david_shishir
6 - Meteoroid

yes please, that would be of great help.

ShankerV
17 - Castor

Hi @david_shishir 

 

Sure, happy to help!!!

Few minutes please.

MatthewO
Alteryx
Alteryx

@david_shishir attached is an example workflow. This approach converts the the column headings to a date field so that the Current Month and YTD can be properly derived at the time the workflow is run. I hope this is helpful for you!

 

image.png

ShankerV
17 - Castor

Hi @david_shishir 

 

Please find the below expected output.

 

ShankerV_0-1672159341346.png

 

Year to Date (YTD) = [(Current Period Value  Beginning of Period Value)] ÷ Beginning of Period Value)

I have used this formula to create the YTD

 

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

 

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

 

Many thanks

Shanker V

david_shishir
6 - Meteoroid

Hi Matthew, Thank you for the solution. Just one issue we need to address is that if we are in Jan of the new year, then the current month should be Dec of the last year and YTD needs to be whole past year. Can you please tweak the workflow to accommodate it so that it would be a fool proof solution.

MatthewO
Alteryx
Alteryx

@david_shishir updating the logic in the Formula tool will accomplish this for you. The attached version has this change. Admittedly, I don't really encourage this type of logic as it's an assumption. My recommendation would be to consider using actual dates in your data set, you can always format them to your liking later in the workflow output.

 

image.png

Labels