community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

MAXIFS equivalent in Alteryx

Asteroid

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:

 

respondentmarketbrandmarketshare
1market1A25
1market1B10
1market1C10
1market1D25
1market1Other30
1market2E15
1market2F45
1market2G25
1market2H30
2market1A15
2market1B25
...   

 

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:

respondentmarketbrandmarketsharemarketshare_bestcompetitorbrand_bestcompetitor
1market1A2525D
1market1B1025A
1market1C1025A
1market1D2525A
1market1Other3025A
1market2E1545F
1market2F4530H
1market2G2545F
1market2H3045F
2market1A1525B
2market1B2515A
...     

 

 

thanks in advance,

Mikis

Asteroid

 

(sorry, redundant post) 

Alteryx Certified Partner
Alteryx Certified Partner

@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

Asteroid

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

Capture.PNG

 

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.

Labels