5 Replies Latest reply on Aug 17, 2016 5:35 PM by kettan

# Calculation inside a measure

Hi all. I know is easy to do calculations between different measure with Tableau, but what about calculation with one measure?

I have an ID Row field that it is supposed to be correlative (1,2,3,4,...). I would like to programm a filter that detect skips (1,2,3,4,6).

I thought that I can do this with a calculation "[ID Row] - [ID Row(-1)] - 1" but I do not know how to it. Then is easy to do the "if" filter to detect differences that are not equal to 0.

In the case I was not clear, Attached is an excel workbook with the ID Row field and the expected result in tableau highlighted.

Thanks all!

Federico

• ###### 1. Re: Calculation inside a measure

LOOKUP is a function to get a prior value. In your case the following might work:

MIN([ID Row]) - LOOKUP(MIN([ID Row]),-1)

That should give you the difference between the current and previous Ids. The Id needs to be aggregated hence I've put MIN, but it could easily be MAX, AVG or SUM, makes no difference as the same number, the ID, will always be returned.

1 of 1 people found this helpful
• ###### 2. Re: Calculation inside a measure

Tkanks, so you add "MIN" in case certains ID appear more than one, dont you?

• ###### 3. Re: Calculation inside a measure

No, I put it there as it needs to be aggregated inside the LOOKUP function

• ###### 4. Re: Calculation inside a measure

I get. Thanks Andrew.

• ###### 5. Re: Calculation inside a measure

If you are querying a relational database, you could probably also use a query like this:

```SELECT "ID row"
, LAG("ID row") OVER ( ORDER BY "ID row" )
, CASE
WHEN ROW_NUMBER() OVER ( ORDER BY "ID row" ) = 1 THEN 'OK'
WHEN "ID row" - LAG("ID row") OVER ( ORDER BY "ID row" ) = 1 THEN 'OK'
ELSE 'See'
END
FROM "Sheet1"
ORDER BY "ID row"
```

See output of your sample data for above mentioned query here:  SQL Fiddle

Ps. If this was relevant, you might also be interesting in having built-in Tableau support of: