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

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

ups366
8 - Asteroid
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 !
5 REPLIES 5
ups366
8 - Asteroid

sorry, pic in here.

row.png

RolandSchubert
16 - Nebula
16 - Nebula

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:

14-12-_2019_09-38-14.png

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

 

Best,

 

Roland

ups366
8 - Asteroid

@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
8 - Asteroid

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
8 - Asteroid

@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.

 

 

Labels