Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-row formula with multiple conditions based on value

HarmeetKM
5 - Atom

Hi there,

 

Is it possible to apply a multi row formula on multiple criteria based on value? I want the formula to populate a serial number on a row where the customer name, item description and value are the same. Although for the value, essentially its "where the value is exactly the same but a negative value" then populate the same serial number.

 

I then want to summarise the dataset so it nets off the positive and negative values for each customer with the same item description. The positive and negative values represent where a product was bought and subsequently returned therefore need to be netted to nil and removed from my dataset

 

Any help would be much appreciated!

5 REPLIES 5
Luke_C
17 - Castor
17 - Castor

Hi @HarmeetKM 

 

Could you provide some sample data? Based on your description I would think this is possible and I don't even think you'd need the multi-row formula.

HarmeetKM
5 - Atom

Sample data attached

Luke_C
17 - Castor
17 - Castor

Hi @HarmeetKM 

 

Take a look at the attached. Prior to using the multirow formula, I created a field that is the absolute value of the amount in order to sort the data. Then I used the multirow formula to fill the serial no if it is null.

 

Luke_C_0-1620657161545.png

 

HarmeetKM
5 - Atom

Thank you - this works perfectly!

fharper
12 - Quasar

AS @Luke_C  says you may not need a Multi-Row tool.  it seems from your description and data that you don't care about "Serial No" just description.  So to get a summary of values at description level a simple summary tool grouped by Customer Name and Item Description, month and Serial No become irrelevant.  And simply summarize the "Revenue".  This gives you the desired summary at customer and description level.

 

I noticed you have different serial no's and values within a common Description thus the second option below.

 

If you want to do this at the "Serial No" level then you have to assume where blank it relates to a non-blank row where the absolute values would match.  

For this, one way is use a formula tool to create an absolute value for the negatives.  The Sort by Customer (ascending), Description (ascending) and Absolute Value (ascending) and Serial no (descending).  This organizes the data so the blank serial no rows will be next to the ones of same absolution value but after the ones with Serial no populated.

 

Then a Multi-Row Formula tools is useful to populate the missing serial no.  For the Serial No field update "If Row-1 (customer & Desc and Absolute Value) are equal to current row values then [Row-1:Serial No] else [Serial-No] endif"

 

Now you have the serial no column populated and can summarize as before but to the level of serial no, adding Serial No to grouping, and not just description.  

 

Labels