8 Replies Latest reply on Jul 27, 2018 6:52 AM by Simon Runc

# Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

Hi all,

I'm a bit of a Tableau novice. I'm trying to replicate some logic that is relatively simple in Excel but can't figure it out in Tableau. I am trying to sum a range of values based on project name and latest date. Both criteria are crucial for the outcome.

To try and explain it in more detail I have created a simple example of the logic in Excel (attached).

Is anyone able to advise on the calculated field formula to copy this over, if possible?

Really appreciate the help!

Regards,

Dan

• ###### 2. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

Good morning and welcome to Tableau

see the attached

it returns this in detail

or this in summary

this is the max date (second condition in your sumif)

this does all the summing based on your first condition

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

While we can do this using Table Calculations, the outcome of this depends on the VizLoD (the Viz Level of Detail)...which is bit like how a pivot table aggregates the data depending on which levels you bring into the Pivot (it's not how it works, but can be a useful analogy for people moving from Excel). but to get the values into the data, so you can use them regardless of the VizLoD we can use LoD Calculations (I'm going to use the FIXED type, as these are simplest to understand)

So first we want to pick up the Global Max Date which is

{MAX([Quarter])}

or

{FIXED : MAX([Quarter])}

...when it's a dimensionless LoD we can drop the key work FIXED

We can then use this in another LoD to get the value per project, just on this date

{FIXED [Project]: SUM(IIF([Quarter]=[Max Quarter Date],[Current Quarter Commitment],NULL))}

I've added RowID so you can see it's working as per the Excel

Hope that that helps and makes (some) sense.

• ###### 4. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

This worked, thank you!

To delve a little more into this. Is it possible to tweak the Max Quarter Date formula so it returns the latest quarter for each project (some projects ended in earlier quarters)? And would this carry over to the second formula so that the latest quarters commitment for each individual project is selected.

Currently some projects are being listed with 0 commitment as they ended before the latest quarter in the data source.

• ###### 5. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

yes you change the formula *** shown

the way you would read the formula in words is "for each project return the max date"

the other formula remains the same

I would suggest you look into the free  training videos at Tableau Training: View Training Courses

they are structured in groups based on topics - short 3-5 min and very good

Please mark this response correct if it answers your question

Jim

1 of 1 people found this helpful
• ###### 6. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

Glad it did the trick...and yes we can. This is where FIXED LoDs are akin to SUMIFS/MAXIFS (they work very differently, under the bonnet, but it can be useful way to think about them)

If you alter the Max Quarter formula to

{FIXED [Project]: MAX([Quarter])}

It will now calculate a MAX Quarter per Project, rather than Globally. The FIXED us telling Tableau what level you want to run the calculation over.

• ###### 7. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

Thanks to you both - it's working exactly how I need now!

I came up to another issue where some projects had the quarters but 0 commitment so it was still returning 0. In case anyone reading is having this issue, I added the bold text below to take the latest quarter without a 0 value:

{FIXED[Project],[Commitment]<>0:MAX([Quarter])}

• ###### 8. Re: Replicating Excel's SUMIFS and MAX formulaes in a Tableau data source as a calculated field

Excellent.

So the calculation [Commitment]<>0...is creating a true/false dimension, and then the FIXED LoD is also taking that into consideration, so if a project ha both zero and positive Commitments, it would create a MAX date for each condition for each project.

Although (as I keep mentioning!) I'm not a fan of using the Excel analogy (as they both work quite differently) this is like creating a column in Excel with the formula (assume Commitment Column is Column B)

=B2<>0

dragging it down and then adding that column to the MAXIFS

Alternatively, you can embed that logic within the LoD

So

{FIXED[Project]:MAX(IIF([Commitment]<>0,[Quarter],NULL))}

And as any rows where [Commitment]<>0 is FALSE will equate to NULL, and the Max of something and a NULL is always the something (in fact the MIN of something and NULL is also the something) it will just pick up the MAX non-zero date.

1 of 1 people found this helpful