Formula reference value of row 5th in field [Sales] or ascending rank 5th in filed [Sales]

[ups366]

Dear all , hello ! I want to use the formula to add a new Column, name as field [Output], the expression = [Sales] - ' value of Row 5th of [Sales] ' , also maybe is = [Sales] - ' value of ascending rank 5th of [Sales] ' , results as the below picture. So, how can i use expression to get the value of row x in field or rank x in field ? Thank you a lot !

[ups366]

sorry, pic in here.

[RolandSchubert · Accepted solution]

Hi @ups366 ,

I think, you want to identify the k-th smallest value in a range and calculate the difference of this value to each other value. The most important issue is to rebuild the logic of the SMALL function in Excel. This can be done by sorting the values (ascending by sales) and selecting the k-th value (5th in your example). You can then append the result to each record and calculate the difference:

I've attached a sample workflow. hoe this is helpful.

Best,

Roland

[ups366]

@RolandSchubert 

Dear Sir, just so so !

Thank you very much !

BTW, I have a other question for formula function, why haven't like max/min/average/small/large/rank formula for field in Alteryx ?

[meeravijayan2011]

Hi @ups366 

In order to calculate the Min, Max and Avg of sales we need a summarize tool. I have modified the workflow of @RolandSchubert. Please find the attached workflow and let me know if you have further questions/ doubts. 

[ups366]

@meeravijayan2011 

thank you a bunch !

IMHO, if we can calculate min/max/avg./small/rank of field directly in the formula, it may reduce the amount of calculation and the user operation is simpler.