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

    Tiago-Mateus Korynek

      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.