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

Alteryx designer Discussions

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

Level of Detail Calculations

Atom

I have a database that collects multiple rows of data for projects over time. Some columns such as Hours Worked (shown below) change over time, others such as Revenue Estimate do not but are duplicated in every row. I am trying to write a formula to generate a new field showing the accurate Revenue Estimate at the project level of detail (Revenue LOD). I cannot remove duplicates within the data because Hours Worked is charted over time. Below is a chart showing an example of the data and the desired calculated field output. Thanks!

 

Example Data Set.PNGDesired Calculated Field.PNG

Alteryx Certified Partner
Alteryx Certified Partner
There's lots of ways you can achieve this, my favourite is by using the summarize tool and group by project ID and return the average revenue estimate.

Ben
Alteryx Certified Partner
Alteryx Certified Partner

Hi, @patd05!
Do you want a new table apart from that, or do you want to duplicate rows for the Revenue LOD field?

 

If you want a new table with just the Project and the LOD, you can use the Summarize Tool, select the Max value for Rev Estimate and group by Projects..

 

 

 

 

Atom

Hi - I would like to have a new field in the same table so that I can use a conditional formula to select the estimates based on the current stage. Similar to using the fixed field in Tableau if you are familiar. 

 

Thanks!

Alteryx Certified Partner
Alteryx Certified Partner

@patd05

 

I think I see what you're saying.
You could use the Summarize tool to group by what you want (Project, Stage), set the calculation (Max, Avg, Sum, whatever)

And then you would use the Join tool to add those values calculated to the original table in each row, creating your Fixed Calc.

 

 

Highlighted
Atom

That makes sense. Is there an easier way to accomplish this using a formula? The calculated field that I am trying to recreate in Tableau is below. The fixed function in Tableau is similar to the Summarize function in Alteryx. Thanks for the help!

 

Calculated Field Tableau.PNG

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@patd05 you can use the Multi-Field Formula to create the IF Statement.
And then use the Summarize Tool to aggregate (Max for each project)

Finally, use the Join tool to append the new results to each row.

 

I'm not sure if there's an easier path for that, but that's how I'd build the workflow.

Aggregating as far as I know is not possible with a single Formula Tool. As it is applied to a row level.

Magnetar
Magnetar

Assuming all of the fields listed in this LOD exist in your Alteryx workflow, you could use the entire IF function in Alteryx with only a few small syntax changes to create a new field in a formula.

Then you could use a Summarize tool to summarize by Project Title, which I think is the best way to accommodate this type of calculation.  This would give you a line by line set of cost savings in one field which you could easily total or select a MAX from.

Alteryx Partner

Hi @BenMoss,

 

I guess average would not be suitable to use in this case, as in project "A", we have revenue estimate of 75 at the "end" stage which has then been considered in the final desired output. MAX can be used instead of AVG in my opinion.

Labels