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

Alteryx designer Discussions

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

Develop a Rolled Up Unit Cost

Meteor

Good Afternoon,

 

My team has been working on a problem for quite sometime now using a large data set, but after numerous attempts but no success of developing results quickly we put together an example in excel. There are 2 data sources attached which include test cost information and unit information. The excel sheet attached also includes those sheets, but our desired outcomes are in the sheet named "Results"  and the calculations are behind those results are in the sheet "Calculations".

 

Our issue is that the costs are at a Cost Type level while our units are at a higher level.

 

There are 3 different desired outcomes...

 

1) Unit Cost by Cost Type and Company and Month

 Essentially each line should just be the total cost for that cost type for that month, by the total units for the month.

 

2) Unit Cost by Company and Month

The same as 1, but cost type is taken out of the equation, this is a sum of the costs for a whole month by company divided by total units for that month.

 

3) Year to Date Unit Cost by Cost Type and Company

Just a sum of the total costs by cost type regardless of month, divided by the sum of all the units for each month.

 

If anyone could help us solve our problem in Alteryx we would be so appreciative, because our real data source is over 50,000,000 lines of data.

 

Thank you,

Justin

Quasar

Hi,

 

Here's a start. I don't quite understand the logic for the last calculation though so get a different result than you. Perhaps I'm missing something obvious.

 

 

2018-05-10_15-10-36.png

Meteor

The first two are perfect, thank you. For the last one though we want the units to be the same for each cost type under the distinct company, I think I know how to do that, but the bigger challenge would be showing how unit cost actually changes month over month from the year to date calculation.

 

Thank you!

Meteor

I have changed the workflow so the last one represents the correct answer, but how could we go about showing the result at the end of Jan, Feb, and Mar as well?

Quasar

A Multi-Row Formula Tool is a good place to start working on Month over Month, Year over Year and YTD formulas. I'll take a look if / when I get a chance

 

Iain

Meteor

Sounds good, thank you!

Highlighted
Atom

How do you combine these different outputs and bring them to one table for Tableau presentation?

Quasar

See Append Fields tool for one approach. Output to TDE

Quasar

Uploading a workflow for your case. 

 

Workflow.PNGResults1.PNGresults2.PNGResults3.PNG

Labels