5 Replies Latest reply on Aug 9, 2018 5:28 AM by Steve PItman

    Availability Calc help

    Steve PItman

      I need to calculate availability Up time/(Up time+Down time) using a data set with the following format:

       

      StateIDDateAvailability (Time up/ (Time up/Time Down))
      UpThing A06-Jul-18
      DownThing A05-Jul-18
      DownThing A01-Jun-18
      DownThing A03-Jun-18
      UpThing A09-Jul-18
      UpThing B01-Jan-12
      UpThing B05-Apr-18
      UpThing B09-Mar-18
      DnThing B29-Jul-18
      DnThing B01-Jun-18

      (repeat this with 8 million rows and 30-40 thousand different items(Thing).)

       

      I would like to calculate availability (time the ID was up in percent) Assuming the ID State remains the same until today.

      This report is pulled randomly with the goal of daily and reports the state of these items state/ID and Date the report was pulled.

      It is a Union of CSV's where the file puller drops the file in a folder and its pulled in and tacked on to the data set.

       

      I'm having trouble Calculating the time between the time the item was up and time it was down(availability).

      Current Calculation that works:

      [Max Date Shown]= Max(Date)

      Min is the  same but min date

       

      Date between Max and min calculation:

      ABS(DATEDIFF('day',[Max Date Shown],[Min Date Shown]))

       

      But I can't figure out how to calculate between those calculations splitting it out by State

       

      Maybe a fancy Lookup( function?

      Like Lookup date diff

       

      Another picture example for one ID

      I need the percent Up(+) vice down over time to assume last State until today.

       

      I appreciate the help. I know this is a difficult one.

       

      I cant include the dataset and sometimes things are reported Up one day. So the calc will need to add the 'day' into the equation.

       

      V/r,

      Pitman

        • 1. Re: Availability Calc help
          swaroop.gantela

          Steve,

           

          I don't think I quite caught the gist, but maybe the attached can give an idea.

           

          I calculated the total days in a state as:

          IF First()=0 THEN 1      // if first day, just give 1 day

          ELSEIF LAST()=0 THEN DATEDIFF('day',ATTR([Date]),[Today])      // if last day, days until today

          ELSE DATEDIFF('day', LOOKUP(ATTR([Date]),-1),ATTR([Date]))      // days between previous day and this day

          END

           

          Then days up would be:

          IF ATTR([State])="Up" THEN [DaysInState] END

           

          And Availability would be

          WINDOW_SUM([DaysUp])/WINDOW_SUM([DaysInState])

           

          All are calculated using the settings shown below.

           

          Please see workbook v10.3 attached in the Forum Thread.

           

          277601updn.png

          1 of 1 people found this helpful
          • 2. Re: Availability Calc help
            Steve PItman

            Thank you I will plug this into my Dataset and see how it looks.

             

            Definitely over my head.

             

            Can you explain the =0 part of the calculation?

             

            I'll be seeing how this behaves in my Dataset i'll get back with you.

            • 3. Re: Availability Calc help
              swaroop.gantela

              Steve,

               

              The FIRST() and LAST() are table calculations that return a relative position:

              Table Calculation Functions

               

              The link states that FIRST() "returns the number of rows from the current row to the first row in the partition".

               

              In the above usage, the FIRST()=0 is checking to see if the number of rows from the first

              in the partition is 0, meaning if it is the very first row.

              That is needed in this calculation because we are doing lookups as to what the previous row's date was,

              and since the first row doesn't have a previous row, it will return null. So we put in a separate

              conditional for the first row.

              Likewise for the Last row, we are need a separate conditional for time till Today.

               

              The importance about the "partition" part is that we need to tell Tableau how the data is grouped

              and how traverse it to know when to restart the count. That is shown in the screenshot above.

              1 of 1 people found this helpful
              • 4. Re: Availability Calc help
                Steve PItman

                This calculation woks great is there a way to make it a LOD? Im trying to be able to break it out by ID and roll it up to total as well.

                • 5. Re: Availability Calc help
                  swaroop.gantela

                  Steve,

                   

                  I'm glad that was helpful.

                   

                  With regards to aggregation, I think the roll ups you seek can be

                  done with further levels of Window Calculations or just with changes

                  to the table calculation settings. If you would specify the levels

                  that you are seeking to aggregate, we can give it a try.

                   

                  It feels to me that the very nature of needing to make calculations based

                  on the previous date will prevent the straight use of LODs. But I could be wrong.

                  Here is one method that uses LODs, but the lookback requires a self-join

                  (probably not suitable for your dataset):

                  Re: Single dimension member and window median on dual axis

                  With LODs, there may also be a need to take into account performance effects:

                  LOD calc and Extract: How can I improve performance?

                   

                  Here is a general discussion of a similar type of problem (though

                  not your specific use case):

                  Calculating ADC in Tableau Server Professional Edition 10.5.2

                   

                  The other method mentioned in that thread is joining to an external

                  running list of dates, but given the size of you data, that would

                  likely be prohibitive.

                  1 of 1 people found this helpful
                  • 6. Re: Availability Calc help
                    Steve PItman

                    You are a Gentleman and a Scholar Sir,