4 Replies Latest reply on Jan 15, 2019 9:32 AM by Heidi Kiser

    Current Assets in a Date Range

    Heidi Kiser

      My Excel data is laid out like this, such that it contains all currently active aircraft and scheduled orders in the future. I'm using Tableau 10.3.

       

       

      I need to be able to see the Net Fleet (sum of current aircraft plus deliveries minus retirements) during any number of years 2018 through 2029.

       

      My desired outputs look something like this:

      which is a sum of active aircraft over a number of years or on a per year basis like this.

       

      I would like to be able to switch between descriptive variables: OEM, Data Utility, Class, Region and Subregion for these graphs without having to redo the calculations each time.

       

      I looked up instructions on how to do this and came across this, but it is not working. Notice in my data each record contains a unique sum of aircraft, not a single one. So I need any calculation to Sum the Quantity.

      https://kb.tableau.com/articles/howto/showing-records-that-fall-within-a-period-of-time

       

       

      I am attaching a packaged workbook so you can see what I've tried so far.

       

      Thank you for your assistance in advance!

       

      Heidi

        • 1. Re: Current Assets in a Date Range
          Jim Dehner

          Hi Heidi

           

          I opened your book - I have used that particular solution before and found it to be difficult to get right - it relies on a number of nested table calculations that need to created and used in a specific sequence

          But at the convention this year  at tc18 Archana Ganeshalingam and Sasha Singh did a session on creating date scaffolds that I think is a more straight forward approach - it is recorded and has several step by step examples -

           

          see the link Solving tough time-based problems with skeleton tables and Tableau Prep - YouTube

           

          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.

          • 2. Re: Current Assets in a Date Range
            Heidi Kiser

            Jim,

            This video goes really fast and assume I have a date_dim$ file setup in advance. I cannot find anything that tells me how a date_dim$ file needs to be setup. Can you advise?

             

            Heidi

            • 3. Re: Current Assets in a Date Range
              Jim Dehner

              Good morning

               

              All you are doing is creating a list of all the dates that you need in your analysis (ALL the dates including the ones where you don't have any records) - so it is created at the level you want to see and will also match the date level of your data   your data is all in years so you list (scaffold) will be all the years (I would use a real date like 1/1/2019) from the first year in your data to the last year your want to include in the analysis - eg if you want to extend it out to 2025 for expected deliveries then include each year 2020 -2025)

              make it a single column in excel and with any heading and join it to your existing data on the actual date column in your data (is year the actual data or is there another?)

               

              the video is easy enough to pause - so take it a step at a time -

               

              Jim

              • 4. Re: Current Assets in a Date Range
                Heidi Kiser

                I figured out how to create a date_dim$ source file on my own. For those reading after me, here are the instructions.

                 

                1) Create a new Excel file

                2) Label the 1st column "Calendar"

                3) Create a list of dates in Day format "1/1/2019" regardless of whether you care about days, weeks, months or years. Increment the dates in the step you require. I only required years, so I used "1/1/2018", "1/1/2019", etc.

                4) Save the files as date_dimension

                 

                I hope this helps users who read this after me.