7 Replies Latest reply on Jul 6, 2016 11:34 PM by Yuriy Fal

# How can I calculate the total duration of readings at a certain value in a time series?

In the attached data set (which is a time series of temperature measurements from three different cities), some of our sensors went offline for a while. I need to calculate and tabulate the total amount of time our sensors were offline.

What I ultimately need is the following cross tab or table:

Chicago5
LA4
New York1
TOTAL:10

Chicago has 5 consecutive readings of 0 (one reading / minute) for a total of 5 minutes offline.

LA was offline for 2 readings of 0 (but these two readings are 4 minutes apart) for a total of 4 minutes.

New York has a single reading of 0 for 1 minute.

Can someone please help me figure out how to calculate the total time my time-series streams were at 0?

• ###### 1. Re: How can I calculate the total duration of readings at a certain value in a time series?

Hi David,

It is unclear if a zero reading

is the same or not.

There is a gap for NY from 8:18 till 8:23 (incl).

How could one interpret this gap?

As for LA, if one would count together

the boundary zero points at 8:23 and 8:27 --

and the 3 missing points in between --

it would give a total of 5 minute points, not 4.

So the counts would depend on rules (implied or articulated).

With that in mind, please find the attached.

Yours,

Yuri

• ###### 2. Re: How can I calculate the total duration of readings at a certain value in a time series?

Thanks for the help so far..

Good point and thanks for the clarifying question.

And a missing point of the data set is not the same as 0.

Let's set up a rule this way... All values once measured are assumed to be constant until measured again. So if a temp is 80 at 8:01, and the next reading happens to be at 8:06, we assume (due to our rule) that the temperature was 80 for 8:02, 8:03, 8:04, 8:05 until we got another reading at 8:06.  The reading that came in at 8:06 may also be 80 or another value. But until we get another value we assume things are as they were at our last measurement.

• ###### 3. Re: How can I calculate the total duration of readings at a certain value in a time series?

I noticed you added the following filter:

first()==0

What does this do? Why and how did you create it and for what purpose? I notice if I take it out the table looks all blurry.

with this filter

without this filter

• ###### 4. Re: How can I calculate the total duration of readings at a certain value in a time series?

Hi David,

FIRST()==0 is a Table Calculation filter.

When TRUE it leaves only one (first) Cell per Addressing window

(the addressing window includes only [Time] in this case).

As a result, only one value is left on a view for each [Name].

If one removes the filter, more Marks are drawn

on top of each other, hence blurring a view.

Yours,

Yuri

• ###### 5. Re: How can I calculate the total duration of readings at a certain value in a time series?

Hi David,

Yours,

Yuri

• ###### 6. Re: How can I calculate the total duration of readings at a certain value in a time series?

How do you create a table calculation filter directly?

I achieved the same result by creating a calculated field and then added that measure to my filters card, but it looks like you created the table calculation filter directly (with no name even?).  Can you explain the mechanics of how you created it?

• ###### 7. Re: How can I calculate the total duration of readings at a certain value in a time series?

Hi David,

This is called "in-place calculation editor".

You just double-click on an empty space

somewhere on a view (on Rows, Columns or Details)

and start typing your calculation, then hit Enter.

If calc syntax is right Tableau makes a pill on-the-fly.

Hope this helps.

Yours,

Yuri

PS I prefer typing on Details :-)