3 Replies Latest reply on Sep 25, 2012 7:31 AM by Janet Shen

    Trying to get a total of all items between two dates using two date measures.

      Hello Everyone,

      I am attempting to get a total of all items that are open during a certain month. Each item has an OPEN_TIME and CLOSE_TIME date measure. For example, if I was looking for open items during January then I would need items that have an OPEN_TIME on or before January and a CLOSE_TIME that is either not listed or after January. This should give me the amount of items that were open at the end of January. I will need a calculation that will give this for each month after a given time (In this example, each month since January 2012). Any and all help would be appreciated.

       

      Thanks

        • 1. Re: Trying to get a total of all items between two dates using two date measures.
          Mark Holtz

          Not sure where you are in the process here, but whenever I have to create a report around "balances" (think beginning balance, opened, closed, ending balance), I structure the data in a specific way.

           

          This may or may not help, but I think it's one way to accomplish what you're after.

           

          Start by making a table which has just a single column--the time interval you're interested in.

          Sounds like for you, it'd be months.  We often use days (with no timestamp data).

           

          So

          1/1/2012

          2/1/2012

          ...and so on

           

          Then, in Tableau, connect to a custom SQL (I'm familiar with SQL Server) data source which pulls that table with a LEFT JOIN to all your results records.  The idea here is that you want to evaluate every record for every possible "time interval" to determine whether the record was open or closed during that time--here are cases statement to do this:

          (## is the time interval of your Dates--mm if you want to evaluate a full month.)

           

          CASE WHEN [StartTime] >= [Date] OR [EndTime] < DATEADD(##,1,[Date]) THEN 0 ELSE 1 END AS BeginningBalance

           

           

          CASE WHEN [StartTime] BETWEEN [Date] AND DATEADD(##,1,[Date]) THEN 1 ELSE 0 AS Opened

           

          CASE WHEN ISNULL([EndDate],'12/31/9999') BETWEEN [Date] AND DATEADD(##,1,[Date]) THEN 1 ELSE 0 AS Closed

           

          CASE WHEN [StartTime] < [Date] OR [StartTime] BETWEEN [Date] AND DATEADD(##,1,[Date])

          AND ISNULL([EndDate],'12/31/9999') >= DATEADD(##,1,[Date]) THEN 1 ELSE 0 END AS EndingBalance

           

           

          Hope this is useful...

          1 of 1 people found this helpful
          • 2. Re: Trying to get a total of all items between two dates using two date measures.

            I believe this has pointed me in the right direction, but I still can't get the data to show up correctly. A little background:

             

            -All the data is from an Oracle database. I'm not sure if this has an effect on how to resolve the issue, but felt it important to mention.

             

            -I'm quite new to Tableau, so I'm still familiarizing myself with all the functions available. On that note, I'm not sure how exactly to join a custom table of dates and time intervals with the Oracle data.

             

            Once again, any help is appreciated. Thanks.

            • 3. Re: Trying to get a total of all items between two dates using two date measures.
              Janet Shen

              Hello Charles,

               

              Would you mind posting a packaged workbook (.twbx file)?  It would be helpful to know how the workbook is currently built.

               

              Cheers!