CODEWITHSUNDEEP
tableau-api
adura826
adura826

Reputation: 113

How to divide an Aggregate and Sum function

I am working in Tableau and trying to create a formula that will return me the value of each customer that walks into a store by dividing Net Sales / Traffic. When I try to combine the two separate formulas, it gives me the following error: Cannot mix aggregate and non-aggregate arguments with this function. The two functions I created that I'm trying to divide are:

SOT = (SUM([Sales Net])-SUM([Sales Gcard Net]))/SUM([Traffic Perday]) and SOT Goal

When I look at it in Tableau, it's stating that SOT is an aggregate function. How do I work around this to be able to get

SOT / SOT Goal

Upvotes: 0

Views: 841

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

Aggregate variables are values that are calculated in the view, and depend on the level of aggregation in Tableau. e.g. sum(Sales) will show different values in Tableau if it’s next to a Region dimension, or if it’s next to a Category dimension.

In order to avoid the errors you can use many solutions. My favorite is indeed LOD expressions. In your view, though I do not have required sample data and therefore, I cannot try my hands on different possibilities here, I suggest that this should work-

SOT = ({SUM([Sales Net])}-{SUM([Sales Gcard Net])})/{SUM([Traffic Perday])}

Do remember that this solution will over-ride your filters and if you are using filters you have to add all those to Context.

EDIT

While trying different possibilities remember these things...

  1. {SUM([Sales])} will sum the sales over entire data and {} i.e. curly braces wrapped around the sum function will cause to return the value as non-aggregate. In other words, this will work as LOD and if you'll add this field to view, the sum of entire sales will be shown against each row.

  2. {FIXED [DIMENSION NAME] : sum([Sales])} will sum sales separately for each Dimension value. Fixed statement (LOD) again returns the value as non-aggregate value. if you'll add this field to view, the sum of entire sales for that dimension will be shown against each dimension.

Upvotes: 1

Related Questions