Alteryx Designer Discussions

ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Level of Detail Calculations

Highlighted
5 - 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!

Highlighted
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
Highlighted
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..

5 - 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!

Highlighted
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
5 - 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!

Highlighted
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.

Highlighted
14 - 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.

Highlighted
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