How to calculate Year to Date

snandy2011

Hi,

I am newbie in alteryx. This can be a stupid topic.But i failed to achieve this properly,

So I need some help from you.

I want to calculate Year to date based on my input data.

Attached is my input data and the screenshots of my output. (Please find the attachments).

I have tried to use multi row formula tool (where expression is [Row-1:YTD] +[Sum_Total Sales])to calculate YTD but failed to achieve desire output.

Below is my try.(Find workflow screenshot).

Can you please help me on this?

Any suggestion is really appreciable.

Thanks,

snandy2011

output data screenshot

Workflow Screenshot

sample data.csv

Best Practices

Transformation

Help

Accepted answers

neilgallen

I believe the attached workflow would get you there. You don't need to use a multirow formula tool here, as there is a running total tool already available in Alteryx. You just need to make sure your data is sorted and then the running total tool is aware to restart for every year.

YTD Sales.PNG

YTD Sales.yxmd

All comments

rafalolbert

Hi @snandy2011,

This should do the trick:

- assign date type to your date field

- extract number value from your value filed (string including $ sign)

- extract year from date to group by in the Multi-Row Formula tool

- sort (this is just in case and best practice as the following tool depends on data being sorted)

- Multi-Row Formula creating running total at year level

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

workflow.yxzp

snandy2011

HI @rafalolbert,

Thanks for your reply and solution.

The solution which you have sent , I have already achieved that by myself before posting here.

But, I am looking a solution which will give me year and monthwise total sales andtheir YTD.

just like the screenshot which i have shared over here.

Anyway, Thank you very much for your time and solution.

Thanks,

snandy2011

estherb47

Hi @snandy2011

Looks like my approach is very similar to @neilgallen

The key to getting Running totals to work is to make sure you're sorted correctly. I did this by adding a First of the Date field into my Summarize tool.

Because your date field is already in the correct syntax of a date, the Select tool can easily convert it. Then a formula tool pulls out the year and month.

Let me know if that helps.

Cheers!

Esther

YTD.yxmd

jaynetan

hi @estherb47,

how will the calculation of MTD values look like in the workflow, using @snandy2011 example.

thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703