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

Moving Median

JamesBills
8 - Asteroid

Hello Community,

I want to calculate how the Median has progressed over time. Other than splitting the data up by the date, then calculating median for each group of data, is there another way to do this? 

 

I have 50+ dates and values, so will to time consuming to split up data manually then do the calculation with a summarise tool. Ideally I want to calculate the median, then add in next value, recalculate median...

 

ie. A median for every line. 

image.png

 

Thanks for your help in advance, 

James

10 REPLIES 10
Ladarthure
14 - Magnetar
14 - Magnetar

Hi,

 

not that good in math, but I think it's more likely to be an average/mean? could you do the trick on excel and send the file to make sure of what you want?

MarqueeCrew
20 - Arcturus
20 - Arcturus

If you use a CReW macro (http://www.chaosreignswithin.com/p/macros.html), it is easy.

 

The Moving Summarize tool allows you to choose which function you want to move with.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danilang
19 - Altair
19 - Altair

Hi @JamesBills 

 

How are you calculating the values in your median column?    From your post...

 


Ideally I want to calculate the median, then add in next value, recalculate median...

This workflow does that, calculating the monthly average by using a running total and dividing this by the number of months since the start.

WF.png

 

However, the results diverge from yours starting in April.  

 

REsults.png

 

Dan

JamesBills
8 - Asteroid

I had looked at this macro, but since you had to specify a set number of "rows back" it didn't work.

As, in my use, I need to include all previous rows. 

JamesBills
8 - Asteroid

The total of the values divide by the number of records would just be the Mean. 

Whereas i am looking for the Median. 

So sort all the values, then take the middle value. 

danilang
19 - Altair
19 - Altair

Hi @JamesBills 

 

Using the sort and middle value approach the results should be 

 

Median.png

 

Is this what you're looking for?

 

Dan

JamesBills
8 - Asteroid

Yes. Apologies made error in my excel. This is what I am looking for

danilang
19 - Altair
19 - Altair

Hi @JamesBills 

 

Here you go.  I had to venture into macro land for this one.

 

The main WF just sets things up for the batch macro

 

Macro.png

The control parameter modifies the Filter tool with each iteration.  The 1st one is [RecordID]<=1, 2nd is [RecordID]<=2, etc and the Summarize tool calculates the median for all records available in this iteration. The output of each iteration is unioned with the previous ones resulting in 

 

Results v2.png

 

 

Dan

 

JamesBills
8 - Asteroid

Thanks Dan

 

That image was exactly what i was thinking, thank you. Unfortunately i can't get into the workflow as I am still on 2019.1.6. 

Any chance of sending over in a different format?

Labels