-
1. Re: Calculation depending of next value
Jim DehnerSep 5, 2019 4:33 AM (in response to Nicolas Journeaux)
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
Nicolas Journeaux Sep 6, 2019 6:18 AM (in response to Jim Dehner)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
Jim DehnerSep 6, 2019 3:09 AM (in response to Nicolas Journeaux)
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
Nicolas Journeaux Sep 6, 2019 7:15 AM (in response to Jim Dehner)Thank you Jim for your answer.
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 1 Serie 2 1 2 2 3 3 4 4 5 5 6 6 Null Nicolas
-
5. Re: Calculation depending of next value
Jim DehnerSep 6, 2019 8:22 AM (in response to Nicolas Journeaux)
if you post your book I will look at it
Jim
-
6. Re: Calculation depending of next value
Michel Caissie Sep 6, 2019 11:07 AM (in response to Jim Dehner)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
-
Book(mc)_v10.5.twbx 14.1 KB
-
-
7. Re: Calculation depending of next value
Jim DehnerSep 6, 2019 11:18 AM (in response to Michel Caissie)
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
Nicolas Journeaux Sep 10, 2019 5:13 AM (in response to Michel Caissie)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
Michel Caissie Sep 10, 2019 2:58 PM (in response to Nicolas Journeaux)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
Nicolas Journeaux Sep 11, 2019 5:35 AM (in response to Michel Caissie)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
Michel Caissie Sep 11, 2019 12:26 PM (in response to Nicolas Journeaux)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.
-
12. Re: Calculation depending of next value
Nicolas Journeaux Sep 23, 2019 6:54 AM (in response to Michel Caissie)Thank you all, sorry for my late reply I was on leave.
Indeed it was my mistake, It's working fine now.
Michel I selected your answer as the correct one.
Thank you again Michel and Jim.
Nicolas