Cumulative Average

[ZoeM]

Hi!

I have a dataset of Dates and days late.

How can I calculate a cumulative average field that I will use in a line chart?

[binu_acs]

@ZoeM, can you post the formula for calculating the cumulative average and the expected result?

[KGT]

There's many ways to do this, what is the cumulative average based on? First up will be converting the date if it comes in as a string. DateTimeParse() is what you want for that. Then, either summarise, or Sort/RunningTotal. Depending what you are after.

Simple solution for just the cumulative average on this dataset, would be a

• Sort (By Date),
• RecordID (used as a counter for how many records in the average).
• Multi-Row Formula to get the Cumulative Average: ([Pre/Post Bus. Days]+[Row-1:CumulativeAvg]*[Row-1:RecordID])/[RecordID]

Please correct your field names before using them in a formula though (double spaces, punctuation etc). That's a request from future users of your workflow.

[nikolinamilincevic · Accepted solution]

Another option is to use single R tool to get cumulative average, once you have data whose cumulative average you want. Assume that you need a cumulative average of a column called "days".

Then you could write in R tool:

data <- read.Alteryx("#1", mode="list")
data <- as.numeric(data$days)

avg <- c()

for (i in 1:length(data)) {
 my_var <- data[1:i]
 avg <- c(avg, mean(my_var))
}

write.Alteryx(avg, 1)

Let me know if this helps :)

[ZoeM]

Thanks for the response. I have not had to use the Multi Forumla Tool so I am not versed in configuring it for this purpose.

Is this something you can help with?

[apathetichell]

hey ---> multi-row formula will definitely work. another way is sort your dates, use a recordid, use a running total. use a formula to divide your running total by your record id. This is your basic cumulative sum/n entries formula.

[KGT · Accepted solution]

Sure, take a look at the attached. I added the solution by @apathetichell as well. 

[apathetichell]

@ZoeM --- can you chime back in --- confirm that everything is working and accept a solution or two or three.

[ZoeM]

This is super awesome, bug thanks for the support. 

Just tweaked it a bit for my workflow but getting the expected result!