1 of 1 people found this helpful
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.
Tkanks, so you add "MIN" in case certains ID appear more than one, dont you?
No, I put it there as it needs to be aggregated inside the LOOKUP function
I get. Thanks Andrew.
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: