MAXIFS equivalent in Alteryx

[Mikis]

Hello,

I'm sure this is a really basic question, but I can't figure it out.

I have a dataset that looks like the following:

respondent	market	brand	marketshare
1	market1	A	25
1	market1	B	10
1	market1	C	10
1	market1	D	25
1	market1	Other	30
1	market2	E	15
1	market2	F	45
1	market2	G	25
1	market2	H	30
2	market1	A	15
2	market1	B	25
...

What I want is to add the marketshare & brandname of the next highest brand in that market.

If a brand itself is the highest, then take the second highest.

I also want to exclude brand "Other" from the results.

looking at respondent1 and market1, brand A & D are tied for first place.

For brands B & C, you can take either brand (A or D), but for brand A it needs to be brand D and for D it needs to be brand A

If there are 3 brands with the same score, you can also take either brand as long as it's not the same brand as in that record

My outcome should look like this:

respondent	market	brand	marketshare	marketshare_bestcompetitor	brand_bestcompetitor
1	market1	A	25	25	D
1	market1	B	10	25	A
1	market1	C	10	25	A
1	market1	D	25	25	A
1	market1	Other	30	25	A
1	market2	E	15	45	F
1	market2	F	45	30	H
1	market2	G	25	45	F
1	market2	H	30	45	F
2	market1	A	15	25	B
2	market1	B	25	15	A
...

thanks in advance,

Mikis

[Mikis]

(sorry, redundant post) 

[BenMoss]

@Mikis why is this a redundant post? Did you create your own resolution? If so be sure to share it and mark it as the solution so others can see in the future.

Ben

[Mikis · Accepted solution]

Hello,

No it was a redundant post because I added comments to my original post, and then I thought to just rewrite the original post (making the comment redundant).

I didn't really know how to delete the comment so I wrote something silly

That being said, I just found a way to make it work (cf attached) :)

I sorted on share, and then used a multi-row formula

Basically: if share is sorted descending, then a new respondent means that record has the highest share

The second row after this one is automatically the second highest share

if [respondent_measurement] != [Row-1:respondent_measurement] then 1 elseif [respondent_measurement] = [Row-1:respondent_measurement] AND [Row-1:best_competitor] = 1 then 2 else 0 endif

My only unresolved issue is that I actually wanted to make my multirow formula a text (I wanted "best_competitor" and "second_best_competitor", not 1 and 2). However, I kept getting error messages until I converted them to 0, 1 and 2.