9 Replies Latest reply on Dec 12, 2017 1:03 AM by Tiago-Mateus Korynek

# Week Number Calculation of Percentages Based on Two Data Sources

I am pretty new to Tableau and have only recently been on a course to learn the fundamentals, however my first project at work is to create a very complex dashboard which is definitely throwing me in at the deep end.

I have two Data sources that I will be using for my analysis one Excel spreadsheet called Delayed Flights and the other spreadsheet called All Flights Flown. These sheets are related in that they share Flight ID which is a text string made up from the [Date][Airport][FlightNumber] e.g 17APR17JFK3456.

I have joined the spreadsheets with a Right Join with Delayed Flights on the left and Flight Statistics on the Right. (Is this even correct for my aims below?)

I am trying to create a calculated field which works out the Week Number (in ISO format which I am having trouble getting even after reading various previous discussions).

This Week Number would then be used in various calculations, but the first calculation I am trying to do is to calculate a percentage rate of how many flights were conducted without a delay using the Delayed flights there have been in a certain Week Number divided by how many Flights there were in the All Flights Flown sheet for that same Week Number. Flight ID comes from the Delayed Flights and Flight ID (All Flights Flown) comes from the All Flights Flown

Essentially the equation I am wanting to create is something like:

1-(COUNT([Flight ID])/COUNT([Flight ID (All Flights Flown)])) which would then be formatted as a percentage.

I have tried playing around with Calculated fields, but now that I am trying to use 2 different sources I seem to be selecting something incorrectly as I am getting nothing that makes sense to me.

How would I go about successfully accomplishing this? I am needing the following things creating:

ISO European Standard Week Number

Something which does the same as 1-(COUNT([Flight ID])/COUNT([Flight ID (All Flights Flown)]))

All help is greatly appreciated and I can provide further information / details just ask.

• ###### 1. Re: Week Number Calculation of Percentages Based on Two Data Sources

Hi

Yes this sounds complex - please attach your workbook in a twbx format so we can see your data and your approach - it will help in resolving the issue - you will probably need an LOD expression but with 2 data sources that could be a problem

Jim

• ###### 2. Re: Week Number Calculation of Percentages Based on Two Data Sources

Hopefully this has uploaded correctly.

• ###### 3. Re: Week Number Calculation of Percentages Based on Two Data Sources

Hi

Actually wanted to see your Tableau workbook and what you have done with the join and the calcualtion

Please attach a TWBX workbook - Packaged workbooks: when, why, how

• ###### 4. Re: Week Number Calculation of Percentages Based on Two Data Sources

Sorry I don't know what I was thinking at the time, my brain clearly stopped working as I was actually planning on writing a message in my reply to say that me sending you my TWBX workbook would have been pretty pointless as literally all I had done was create a Right Hand join of the two spreadsheets with Delayed Flights on the left and then an in-hope bash at calculated fields. Even then I had probably done something incorrectly no doubt.

Is this data something you can work with and understand what I am trying to achieve? Anything you need me to explain?

• ###### 5. Re: Week Number Calculation of Percentages Based on Two Data Sources

Tiago-Mateus Korynek, uploading a .twbx would be really helpful in trying to see what you are trying to accomplish in terms of using Tableau's native features.  I think I kind of understand what you're looking for, but a .twbx would help.

• ###### 6. Re: Week Number Calculation of Percentages Based on Two Data Sources

My calculated field that I am using in my workbook is now called Rate of Successful Flights as hopefully this will make it easier to explain to people jumping on to help part way through my project.

The formula I am using for this is 1-((COUNTD([Flight ID]))/(COUNTD([Flight ID (Flight Statistics)]))) although I have a feeling there should be a better way of working this out from my data.

My data is collected in such a way that the FlightID is always unique to a specific flight. Also if I had 4 flights in a week that were delayed, there would be 4 records in my Delayed Flights data no more and no less and these records would match up perfectly with flights in the Flight Statistics sheet.

When I take Scheduled Departure Date from the Flight Statistics Table and drag it to Rows as a Discrete Dimension and use my calculated field as an Aggregated Continuous Measure I get something which looks like the rate that I want as it looks like a value ranging from 0-1. As the Scheduled Date goes Year, Quarter, Month, Day is there any way I can put Week inside of Month and Day? And also make it follow ISO week numbering rather than Tableau's Standard Numbering?

Hopefully this workbook has what you require from me. sorry for the delay in replying I had a long weekend out of the office...

Edit: has this helped Nathan Anderson?

• ###### 7. Re: Week Number Calculation of Percentages Based on Two Data Sources

Any further help?

• ###### 8. Re: Week Number Calculation of Percentages Based on Two Data Sources

Tiago

Please see if the attached could be a starting point.

I added in a third sheet which is just a list

of all the Week Starts and Week Ends:

WeekStart|WeekEnd

11/27/2016|12/3/2016

12/4/2016|12/10/2016

12/11/2016|12/17/2016

...

Flight Stats was joined with Delayed Flights via a Left Join

and the WeekLookup was joined on a calculated field of 1.

To filter down the flight and put it in the correct week:

[Scheduled Departure Date]>=[Week Start]

AND

[Scheduled Departure Date]<=[Week End]

Binary Delay Flag:

IF NOT(ISNULL([Delay Time]))

THEN 1 ELSE 0 END

Delays per week:

{ FIXED [Week Start (Week numbers)]:SUM([Delay Flag])}

Flights per week:

{ FIXED [Week Start (Week numbers)]:COUNTD([Flight ID])}

Percent delayed:

[Delay per Week]/[Flights per Week]

• ###### 9. Re: Week Number Calculation of Percentages Based on Two Data Sources

Unfortunately I am unable to look through the workbook at the moment as I am away from my PC with Tableau, but I hope to have a look through it in the next 3 or 4 days.

Your help has been awesome. Thank you very much Swaroop for your effort.

1 of 1 people found this helpful