Percentile aggregation

[Wangwn]

Is there any Alteryx function that can recreate the following statement?

percentile(CAST(substr(value, 5) AS INT), array(0.0, 0.5, 0.99, 1.0))

[jdunkerley79 · Accepted solution]

You would need to do this in a couple of steps

First CAST(substr(value, 5) as INT): This can be done using a formula tool. You can specify the type of the output as an Int32 and then the expression:

ToNumber(SubString([Value], 5))

You can then use a summarize tool to calculate the percentiles.

Sample attached

[Wangwn]

Thank you!

[Kate]

Is it possible to use a field input to determine the percentile value that the summarise tool calculates instead of having this hard coded as 25% for example?

Thanks

[jdunkerley79]

You could wrap the Summarise tool into a batch macro and have it read in. It is easy enough to generate the needed XML for the tool. 

It might, however, be easier to go back to first principles.  Attached a quick sample computing from first principles

[Kate]

I thought that may be the case but very useful to have the macro..thank you! :)