Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

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

Highlighted
6 - 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!

Highlighted
16 - Nebula
16 - Nebula

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

Highlighted
6 - 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.  

Highlighted
5 - Atom

I need to convert excel formula to alteryx formula, Could you please help on this. Appreciate help! 

 

Lower Quartile IRS =IF(MOD(COUNT(F2:O2),4)=0,AVERAGE(SMALL((F2:O2),COUNT(F2:O2)/4),SMALL((F2:O2),1+COUNT(F2:O2)/4)),SMALL((F2:O2),1+INT(COUNT(F2:O2)/4)))

 

Sample data attached

Labels