cancel
Showing results for 
Search instead for 
Did you mean: 

Combining same transaction for average

boston99
Meteoroid

I have transaction data and I am trying to find the average of such numbers. The data is laid out where each product bought is a row - i.e. if a transaction has three items purchased that transaction will cover three rows (and each of these rows will have qty of item bought, how much the item was purchased for, etc). I wanted to do some simple calculations such as average number of items purchased and average amount spent per purchase, but was struggling with how to lay this out in Alteryx.

 

Happy to clarify anything and really appreciate your help! 

  • Data Investigation
Bob_Blackey
Asteroid

Hi @boston99 ,

 

You are a step ahead of the game as when you have an unknown number of items Alteryx deals with row data better than columns. (if you have columns you usually Transpose to get rows)

 

The key tool here is the Multi-Row Formula  (MRF)  tool (don't leave home without it!)

 

To get the total # qty you use a MRF to create a new field called TotalQty.

 

Then within the formula you use an IF statement to say -

        IF the current row's product number (for example) is equal to the previous row's product # then TotalQty is equal to the previous rows TotalQty + this row's Qty.

        ELSE

                     Set TotalQty equal to this row's Qty (since it is the start of a new product's rows)

        ENDIF

 

You need a second MRF for TotalValue.

 

You can then use a summarize tool to summarize to the product level and use the LAST action to get the last value of TotalQty and TotalValue.

 

Hope that helps! 

Bob

 

alex
Fireball

It would be easier to use the summarize tool.  Go to Help>Sample Workflows>One-Tool Examples>Transform>Summarize to get some cool workflows that really do a great job explaining this tool.

 

I attached a quick sample with some transactions as an example.  Let me know if it helps.