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

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Alteryx Designer: Calculating Quartiles in a Formula Tool Instead of in Summarize Tool

Meteoroid

I need to calculate the 25th and 75th percentiles according to a certain formula used in Excel.  Note, this is different than the normal calculation that is being utilized by the Summarize Tool.  The Excel formula is as follows:

 

IF(C15=0,#N/A,IF(MOD(C15,4)=0,AVERAGE(LARGE(C4:C13,CEILING(C15/4,1)),LARGE(C4:C13,CEILING(C15/4,1)+1)),LARGE(C4:C13,CEILING(C15/4,1))))

 

I know the LARGE and CEILING functions do not exist in Alteryx but I was curious if anyone had any ideas on how to put this Excel formula into Alteryx.  I have been seeing some threads on using the Tile tool to calculate quartiles but I'm not quite sure how that works.

 

Thanks!

You can reproduce this.

 

I am assuming that C15 was the count of values.

2018-12-10_23-26-30.png

 

 

The top path sorts the values into descending order and adds a record id. This will allow the reproduction of the 'Large' function by joining onto the record ID.

Next the value of CEILING(C15/4,1) is reproduces by getting a count of records (assumed to be C15's value) and then reproducing as CEIL([Count]/4)

Next if MOD([Count],4)=0 we need to have both this index and the next one

Finally we join the recordid to the computed index and average the result.

 

This should produce the same values as the Excel formula

Meteoroid

Thank you!!! I added a "+ 1" to the first Index Tool and then a "- 1" to the second Index Tool and I was able to replicate this for the lower quartile.  

Labels