community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to calculate Year to Date

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

unnamed.png

 

Workflow Screenshot

clipboard_image_0.png

 

 

 

 

 

 

Bolide

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 

 

img1.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

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

Highlighted
Quasar

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

Pulsar

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.

image.png

 

Let me know if that helps.

Cheers!

Esther

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

 

Labels