Select highest value

[SCK]

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

[AshleyL]

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]

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

[AshleyL]

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

If this helps, please mark this as a solutions!

Best,

Ashley

[SCK]

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

[AshleyL · Accepted solution]

Here you go! 

Does this help?

[SCK]

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

[AshleyL · Accepted solution]

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:

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:

[SCK]

Awesome, thanks so much for your help @AshleyL  🙂

[SCK]

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.