11 Replies Latest reply on Sep 11, 2019 12:26 PM by Michel Caissie

# Calculation depending of next value

Hello,

I'm trying to test if a vehicle is running with the optimal configuration, I mean with the right number of engines running (I know weird vehicle)

I have the actual number of engines running and I have the optimal number of engine running.

To know if I'm running with the optimal configuration I only need to test if the number of engine running is different from the optimal number of engine :

Running Optimal configuration (1) =

IF [Nb Engine running] = [Nb Engine optimal] THEN

TRUE

ELSE

FALSE

END

But I need a more complicating calculation. I need to know if the optimal configuration will stay the same for the next 20 minutes (2 rows with my timestamp).

I tried the LOOKUP function without success. I was expected something like that:

Running Optimal configuration (2) =

IF [Nb Engine running] = [Nb Engine optimal] AND LOOKUP([Nb Engine optimal],1) = [Nb Engine optimal] AND LOOKUP([Nb Engine optimal],2) = [Nb Engine optimal] THEN

TRUE

ELSE

FALSE

END

It is very frustrating because it is so easy to it an excel with rows but can't find a solution on Tableau.

I couldn't attached a Worksheet because the amount of data is to big (110Mb). If it is really necessary I will try to sort it a make it smaller.

Thank you very much for your help

• ###### 1. Re: Calculation depending of next value

Hi

I am not going try to envision what this vehicle looks like

but you are on the right track with using a lookup function but I think the logic here can is actually     that you want all three values to be the same

IF [Nb Engine running] = [Nb Engine optimal]

AND LOOKUP([Nb Engine optimal],1) = [Nb Engine running]

AND LOOKUP([Nb Engine optimal],2) = [Nb Engine running] THEN

TRUE

ELSE

FALSE

END

now make sure you set the calculation in the right direction based on the table and it should work

BTW  I cam to Tableau from an excel background  - and it took some work to understand the use of dimensions and measures acting like an entire column in a spreadsheet -  lookup  functions are just moving around the table that underlays the specific worksheet (as filtered)  -    in the formula above yo are working with a single record for the running engine   and then moving down ? the table to optimal engine

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculation depending of next value

Thank you Jim for your prompt answer.

I come also from Excel so I still have this vision of value as one row with multiple lines. And it seems so difficult just to create a new value by just shifting an other one.

I tried using Lookup function but I'm always stuck with this error : And I don't understand why does it need to be aggregated if I only want to chek the value just "ahead".

Nicolas

• ###### 3. Re: Calculation depending of next value

good morning

the format for a lookup requires that the field be aggregated  just the way it is so all the fields in the expression must be an aggregate

lookup is a table calculation and tables are the result of bringing dimensions and measures to the canvas which creates aggregates in the process

stand back from your excel background for just a second and think about a calculation in Tableau ( or any other data base orient system) as creating a cell formula in a spreadsheet and simultaneously copying that formula to an entire column (or row) in that spreadsheet -

additionally the order of operation in tableau Table Calculations are at the bottom - makes sense - the table needs to be created and filtered before you can move around the table and select values (as with lookup) or do other calculations like running sum Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Calculation depending of next value

I understand your analogy with the formula in a spreadsheet and the need for the calculation to be done after.

However I can't find the correct aggregation for the LOOKUP function.

In Python I would just shift the data using the index (which is my timestamp).

On Tableau I didn't even succeed to just shift a measure like this (Serie 2 is Serie 1 shifted by 1 index/timestamp):

Serie 1Serie 2
12
23
34
45
56
6Null

Nicolas

• ###### 5. Re: Calculation depending of next value

if you post your book I will look at it

Jim

• ###### 6. Re: Calculation depending of next value

Nicolas,

You still need to aggregate because Tableau cannot assume that you have only one record for a given timestamp.

If you look in the attached, I have a column ID which is unique for every row. Using this I can resolve you problem with

ATTR( [Optimal] ) = ATTR( [Running] ) and LOOKUP(ATTR( [Optimal] ),1) = ATTR( [Running] ) and LOOKUP(ATTR( [Optimal] ),2) = ATTR( [Running] )

compute using Table Down (sheet 2)  or ID  if ID is the only Dimension in the View (Sheet 2(2) ).

If ID is not in the view  then I get more than one record for each  Optimal-Running group  so the computing is done based on each group  and does not resolve your problem.

Sheet 2(3)

So if your timestamp uniquely identify each row, and you have it  in the detail of the view, you can use the above formula  and adjust the  computation based on the structure of the view.

Michel

• ###### 7. Re: Calculation depending of next value

Michel is correct - and it reinforces why we like to see the actual workbook and all the other calculations that go into it

• ###### 8. Re: Calculation depending of next value

Thank you Jim and Michel for your answer.

Unfortunately I'm using Tableau Public and can't open the book Michel sent.

I made a book on Tableau Public so you can have a look at it. Let me know if you can acces the data.

Thank you very much for your help

Nicolas

• ###### 9. Re: Calculation depending of next value

Nicolas,

You can use  [Calculation1]

ATTR( [Nb Engine theoretical optimal] ) = ATTR( [Nb engine running] ) and LOOKUP(ATTR( [Nb Engine theoretical optimal] ),1) = ATTR( [Nb engine running] ) and LOOKUP(ATTR( [Nb Engine theoretical optimal] ),2) = ATTR( [Nb engine running] )

compute using  Timestamp

If you want to show a graph, showing only the periods where the running engine was optimal, you can create another calculation

if [Calculation1] then ATTR( [Nb engine running] ) else 0 end

compute using  Timestamp

and build a graph like this In your dataset , running engine is optimal  only on cases where running engine =1,  so the bar dont get higher than 1. Where there is no bar it is because the engine is not optimal.

Michel

• ###### 10. Re: Calculation depending of next value

Thank you very much Michel !!!

Now it's working (at least it's showing something). However I don't get the result I'm expected. To understand why I'm going step by step using your approach.

To do so I created a new calculation :

LOOKUP(ATTR( [Nb Engine theoretical optimal] ),10)

And ploted it in a graph with Nb engine running. I was expecting to see the same curve just shifted by 10 timestamp. But I get this instead : As you can see it is shifted but the value are not the same. Sometime I have a difference of minus 1, sometime I don't. Any idea why ?

I updated the workbook if you need.

Thank you again for your help.

Nicolas

• ###### 11. Re: Calculation depending of next value

Are you sure you are comparing the same measure.

It looks that you are comparing Nb of Engine Theorical Optimal   with   Nb Engine Running.

What is your formula for the calculation   Nb Engine Running.