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

# Availability Calc help

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

 State ID Date Availability (Time up/ (Time up/Time Down)) Up Thing A 06-Jul-18 Down Thing A 05-Jul-18 Down Thing A 01-Jun-18 Down Thing A 03-Jun-18 Up Thing A 09-Jul-18 Up Thing B 01-Jan-12 Up Thing B 05-Apr-18 Up Thing B 09-Mar-18 Dn Thing B 29-Jul-18 Dn Thing B 01-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

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. 1 of 1 people found this helpful
• ###### 2. Re: Availability Calc help

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

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

Steve,

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

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

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

Steve,

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

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

You are a Gentleman and a Scholar Sir,