Finding and adjusting only the first Max value in a range

[BrianJ]

Hi Community

I need to find the first max value in a range and adjust only that one.

In the attached file I have "Department code" in the first column, "application ID" in the second column and the "% share of costs" by Department code in the last column on the far right column.  The % share of costs by Department code needs to total 100%, but each % by App ID can only be 5 decimals, so I usually have a rounding difference.  I need to identify the Variance by Department code, identify the Max % share of costs value for each Department Code (done in columns 3 and 4), and then add the Variance to the Max value in the "% share of cost" column, which I can do with a simple formula.

My problem is, I don't know how to deal with a situation where there are multiple "Max" values for the same Department code.  In the attached data file, for Department code 23746 the Max value of 0.25 appears twice.  The same situation with Department code 89067 (Max of 0.12682 appears twice).  As a result, my simple formula adds the variance of 0.00001 and 0.00013 respectively to both Max values, thereby causing further differences.  In Excel I just use a Vlookup to find the first value, but I can't find a similar trick in Alteryx

I hope that makes sense.  Any help or direction is appreciated.

[OllieClarke · Accepted solution]

Hey @BrianJ 

A number of different ways of doing this, but here's where my mind went:

The trick is to sort descending by %, and then in the multi-row formula, we can set non-existent rows to NULL, group by Dept Code, and only add the variance if the previous row's dept code is null (i.e. it doesn't exist).

Hope that helps,

Ollie

[BrianJ]

Hi @OllieClarke 

Many thanks for your help.  My Alteryx is a slightly older version so I'm unable to open your solution.  However I followed your notes and couldn't make it work for my full list (I have 20 departments).

Here's what I did:

I added the Record ID and sorted by % share of cost descending.  When I do that I get the max percentages mostly at the top .  However, where I had two Max percentages in the same department, they are now one above the other. 

I then do a Multi-Row formula, creating a New % column, Group by Dept Code and use the formula "IF ISNULL([Row-1:Dept Code]) THEN [% share of cost]+[Variance %]
ELSE [% share of cost]
ENDIF"

What am I missing?

Thanks again for your help.

[BrianJ]

I forgot to mention, I set the Value for Rows that Don't exist to NULL

[BrianJ]

Hi @OllieClarke 

I checked the result using a Summarize function, but picked up the original "% share of cost" column instead of the New % column.  As a result it looked like there were still differences.  However, after working through everything I realized my stupid mistake, updated to the correct column and am very happy to confirm it worked perfectly (I'm not fully sure how yet 😁, but it does).  I have no rounding differences.

Thank you so much for your help.  It really is appreciated.

Brian