12 Replies Latest reply on Sep 23, 2019 6:54 AM by Nicolas Journeaux

    Calculation depending of next value

    Nicolas Journeaux

      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
          Jim Dehner

          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

            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 :

            Lookup.jpg

            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 Dehner

              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

               

               

              Image result for tableau order of operations

               

              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

                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 1Serie 2
                12
                23
                34
                45
                56
                6Null

                 

                Nicolas

                • 5. Re: Calculation depending of next value
                  Jim Dehner

                  if you post your book I will look at it

                  Jim

                  • 6. Re: Calculation depending of next value
                    Michel Caissie

                    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
                      Jim Dehner

                      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

                        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.

                         

                        https://public.tableau.com/views/Demo_15681157241610/Engine?:embed=y&:display_count=yes&publish=yes&:origin=viz_share_li…

                         

                        Thank you very much for your help

                         

                        Nicolas

                        • 9. Re: Calculation depending of next value
                          Michel Caissie

                          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

                            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 :

                            Demo Tableau.JPG

                             

                            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

                              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

                                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