10 Replies Latest reply on Jun 13, 2016 7:23 AM by Mark Fraser

# Percentage of total

Hi all, I need help. Many times I have tried to get a graph like the one I have attached. I have a field with true/false and I would like to display in a graph line, for each day, the percentage of "true" records. In the picture I have attached I have two lines (one for true and one for false), that I have created dragging the true/false field to color and doing a quick table calcuation with percentage of total computing using table down. The problem is that I'm only interested in the percentage for the "true". I don't want to see both. Any ideas?

• ###### 1. Re: Percentage of total

Hi Paula

Fun problem, and a few ways to solve...

Easy way IF statement.

TRUE COUNT = IF [your field] = 'TRUE' THEN COUNT([something])          (you count the activity for true only)

FALSE COUNT = IF [your field] = 'FALSE' THEN COUNT([something])       (as above but for false)

TOTAL = {COUNT([something])} << needs v9                                           (the total, for % calcs)

% = SUM([TRUE COUNT])/ TOTAL

If you had some fake example data, I can try and help further...

Cheers

Mark

1 of 1 people found this helpful
• ###### 2. Re: Percentage of total

Hi Mark,

Thank you very much for your quick response! Very much appreciated.

I'm afraid I'm a quite a beginner. I don't understand where the code goes. Are those calculated fields? Fields editted in shelf?

I have attached a sample file with some data. I just want to see, in a line graph the % of flights that were punctual

Many thanks,

Paula

1 of 1 people found this helpful
• ###### 3. Re: Percentage of total

Hi Paula

Thanks for the sample, we'll do it togehter, slow as you want

First, save the Excel file, open Tableau, left hand side, click Excel, find the file, double click or click open.

You should be here -

The highlighted bit

Column 1 is a date, Tableau has recognized that

2, is a number, count of flights

3, is a string, text.

All is well, we can click sheet 1

With me?

1 of 1 people found this helpful
• ###### 4. Re: Percentage of total

I'll assume you are

Next we need to make some calculated fields.

Hopefully you see this -

Click the arrow, top Create Calculated Field

Give it a name Delay Count, then enter this

IF [Delayed Flag] = 'Delayed' THEN [Flights]

ELSE NULL END

What does that do?

If Delayed flag = Delayed, it returns the number of flights.

If it doesn't equal Delayed, it returns nothing/null.

Exactly the same again, but for punctual.

New calculated field, name = Punctual Count, then enter this

IF [Delayed Flag] = 'Punctual' THEN [Flights]

ELSE NULL END

Stop.

1 of 1 people found this helpful
• ###### 5. Re: Percentage of total

Hopefully you're still with me

One more calculated field - TOTAL

Copy/Paste the below exactly

{INCLUDE [Scheduled Date] : SUM([Flights])}

The {} need v9, they use level of detail

What it does? It says for every day, total the number of flights.

You should now have 3 fields

Delay Count, Punctual Count and Total.

Now its a good idea to check we are OK... I have made this view in Tableau and compared with the original

vs

All is well

1 of 1 people found this helpful
• ###### 6. Re: Percentage of total

Sorry, I'm catching up now.

• ###### 7. Re: Percentage of total

Now we know that's all working... we need to calculate the % of delay per day.

Last calculated field - Delay %

SUM([Delay Count])/SUM([TOTAL])

We now have the building blocks

Here I have created the view

completed v9.3.2 attached.

One thing - the % from your original

These are them calculated in Excel

vs

Tableau matches Excel, but that doesn't match the original.

Hope that helps/ is clear, any questions please come back to me

Cheers

Mark

• ###### 8. Re: Percentage of total

That is amazing!! Thank you very much.

Can I ask one last favour?

What if the flights were not aggregated for punctual and delayed? What if I had a list with a row for each file, like in the file I have attached. How would the process change?

Many thanks

• ###### 9. Re: Percentage of total

Paula Fermin Cueto wrote:

Sorry, I'm catching up now.

I'm leaving shortly, so I wanted to get something written before i go, i can of course pick up questions if you have them

Basically, make the formulas (in italics) and then copy the layout i provided (i.e. date on the column, Delay % on row etc.)

If you want to show off... you can create a parameter so end users can swap between delayed and punctual.

To do this... New calculated field Activity %

IF [Type] = 'Delayed' then [Delay %]

ELSEIF [Type] = 'Punctual' THEN [Punctual %]

END

New parameter

Settings

Then swap Delay % for Activity %.

Right click the parameter (bottom left corner) - show

Sheet 2 attached

• ###### 10. Re: Percentage of total

That is amazing!! Thank you very much.

Can I ask one last favour?

What if the flights were not aggregated for punctual and delayed? What if I had a list with a row for each file, like in the file I have attached. How would the process change?

Many thanks

If they aren't aggregated - rather than return [Flights] you'd just return 1.

So

IF [Delayed Flag] = 'Delayed' THEN 1

ELSE NULL END

IF [Delayed Flag] = 'Punctual' THEN 1

ELSE NULL END

And total swaps from SUM to countd (d for distinct)

{INCLUDE [Scheduled Date] : COUNTD([Flights])}

Should be fine