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

Select highest value

SCK
8 - Asteroid

Hi all. I need help on selecting the 2 highest various values. I have attached the input and output for reference. Please help.

11 REPLIES 11
AshleyL
Alteryx
Alteryx

Hi @SCK --

 

Your input and output files are the same in your post... Can you explain a little more what you are looking to do?

An updated example output would be helpful as well!

 

Ashley

SCK
8 - Asteroid

The output sheet is colour highlighted based on 2 highest values.

AshleyL
Alteryx
Alteryx

@SCK - 

I've attached a workflow that should help you if you're trying to mimic the highlighting capability! You will need to use a Reporting Table.

 

I think you said you wanted to highlight the top variance values instead of the 'various value'? Is that correct? 

 

I created a rule in the Report Table that highlights the top variances using a variance rank field that I created:

HighlightRule.PNG

 

If this helps, please mark this as a solutions!

Best,

Ashley

SCK
8 - Asteroid

Can you please share the ..yxmd extension file? The zip is not working due to version difference,

AshleyL
Alteryx
Alteryx

Here you go! 

 

Does this help?

SCK
8 - Asteroid

Thanks so much for providing the solution @AshleyL . Could you please explain the formula in table?

AshleyL
Alteryx
Alteryx

Yes!

So before I explain the formula, here's what I did overall:

I filtered to only have the rows of data where 'item #' ='total variance', I sorted the data by PY amount & used the Record ID to create a Rank called 'Variance Rank' (since you only want to see top 2). That rank gave me the Total Variance ranked 1-4:

Rank.PNG

After, I bring the data back together with a union and sort the data back into it's original order.

On the bottom of the Report Table building block there is a 'Per Column Configuration'. I clicked 'PY amount' and then to the right, I selected Column Rules. I created a new column rule using a formula to only highlight the 'PY amount' if the rank was less than 3 (ultimately giving me the top 2 variances). I created it so 1 is true and 0 is false. If it's true, I changed the background color to green:

Highlight.PNG

SCK
8 - Asteroid

Awesome, thanks so much for your help @AshleyL  🙂

SCK
8 - Asteroid

Quick follow up @AshleyL . If I need to highlight the 2 highest  absolute variance, how do I do it? I tweaked the input and output a bit. Please help.

Labels