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

[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?

[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

[neilgallen · Accepted solution]

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.

[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

[snandy2011]

Hi @neilgallen,

Thanks for your solution.It is perfect which i was looking for. 

Really big thanks for your solution and time.

As i am newbie in Alteryx, your solution is really helpful for me.

Thanks,

snandy2011

[jaynetan]

hi @estherb47,

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

thanks