7 Replies Latest reply on Sep 6, 2018 8:10 AM by Joe Oppelt

    Getting value from previous row

    Artyom Malafeev

      Hi everyone!

       

      I am completely new to tableau and strugglled for couple days to solve my problems so i cry for help

       

      So i have a larger data set and some data designed like this:

       

      ID        EVENT       TIME    
      1         attempt       [time]

      1         error            [time]

      1         attempt       [time]
      1         success      [time]

      2         attempt       [time]

      2         attempt       [time]

      2         error            [time]

      3         attempt       [time]

      3         success      [time]

       

      Where ID is User unique ID and event is specific action done by user

       

       

       

       

      I have two major problems to solve

       

      Firstly, i need to identify whenever there are any cases for specific users of specific subsequent events and store this information into TRUE/FALSE variable.
      So, for example, i need to check for all users separately if success event is followed by attempt event.
      My initial code was

       

       

      {fixed [id] :

      IIF(LOOKUP(([event]),-1) = "event 1"

      and

      ([event] = "event 2"),

      TRUE,

      FALSE)}

       

      but i get error that all fields must be aggregated or constant

       

      Secondly, i need to check whenever there were specific events accompanied by another event(not necessarily immediately after) for specific user. For example i need to know if user with ID 1 had attempt event and success event and if he or she had i need to store this into TRUE/FALSE variable as well. Have exactly zero ideas how to do that.

       

      I also i have to create variable if specific user had two or more same events but i think it is kind of similar to second thing so hopefully i will figure it out on my own.

        • 1. Re: Getting value from previous row
          Joe Oppelt

          You can't do table calcs (such as LOOKUP) in LODs.

           

          So take the LOD syntax out of there and just do the logic after the colon.  Also you will have to wrap [event] in ATTR() in that calc.

           

          ATTR([event])


          That will clear up the errors.

           

          Whether or not this will give you the results you want will depend on the table calc settings you put on that calc.  If you have the sheet displayed like your example, then TABLE(DOWN) should work.

           

          Actually, if you need it to restart for every user ID, you may need to do a different table calc setting.

           

          Can you upload a sample workbook?  The settings will depend on what your data looks like and what you have on the sheet.

          • 2. Re: Getting value from previous row
            Artyom Malafeev

            Joe, thank you on this one!

             

            Sadly i can't upload workbook since it is clients data - i wish i could.

            • 3. Re: Getting value from previous row
              Joe Oppelt

              I can't help without a workbook.

               

              Consider this:

               

              Video demonstrates how to anonymize your workbook/data

               

               

              You don't need a lot of data.  Just two or three IDs and enough rows within an ID to exercise what you need to have done.  In fact, you can probably toss out all the other columns that aren't needed for the viz you are trying to do.  You don't need all the other sheets and dashboards in the workbook.  Just the sheet(s) you need for this question.

              • 4. Re: Getting value from previous row
                Artyom Malafeev

                Joe thanks, tutorial was really helpful!

                 

                I ve attached my workbook

                I actually don't need any work sheets(i think) in my workbook since i just need to create couple of variables.

                I created variable i needed w/o LOD but it still gives me no correct answer.

                • 5. Re: Getting value from previous row
                  Joe Oppelt

                  See attached.  (I saved it as 2018.1 because my Beta 2018.3 didn't tell me what version your workbook came from.  If you're a version lower than 2018.1, let me know.)

                   

                  On Sheet 2 I took the sequence calc off filters.  I made a [Previous Event] calc and put it on the sheet.  Everything comes up NULL.  That's because tableau's default for the table calc is TABLE(ACROSS).  So basically it's taking every row (for instance 5.296/"about_plans"/first time row) and looking backward within that row for the previous value.  And since there is only one value in that row, there is no previous, so the result is null.  To see that table calc setting, right click on the Previous Event] pill on the ROWS shelf and select "edit table calc".

                   

                  Now go to Sheet 2(2).  Here I edited the table calc for previous event.  Right click and select edit table calc.  See what I have there.  I'm telling it to process through all the dimensions on the sheet, in the order I specified (you can drag the dimensions up and down in the "Select Dimensions" panel).  And I also told it to restart for every ID.  This is important.  I think you do NOT want the first Event of 7,758,333 to look at the last event of 5.296.

                   

                  So you will see that the first [Previous Event] for every ID is always null.  You want that.

                   

                  There is another setting in there.  Look at "At the level".  Initially I had it set to DEEPEST.  But when it's set that way, and the calc is on the second TIME value for "Attempt" under 5.296, it would see the first value of "attempt" and grab that value on the second row.  So I told it to act at the level of [Event].  Now only the first row of a multi-row event will look backward.  The second one sets itself to NULL.  (I can make that set both rows of "attempt" to "about_plans" if needed.)

                   

                  So now you have a calc that looks backward.

                   

                  Next look at [Was my previous....].  Here I compare my current value to the previous event, and set 1 or 0 accordingly.  Note that I have to set the table calc settings for this one too.  Use the same as what we did for [Previous Event].  I compared "fail" and "start".  You can modify and rename this calc to do specifically what you actually need here.

                   

                  And if you drag that calc from the text shelf to filters, you can select for 1 and get only the rows you want to see.

                  1 of 1 people found this helpful
                  • 6. Re: Getting value from previous row
                    Artyom Malafeev

                    Joe thank you!!

                    that was really helpful - i've implemented it and it is working!

                     

                    I would really appriciate if you could help me with second thing.

                     

                    So what i need to do is to understand whenever combination of specific events ever happened for user. For example, i've updated workbook and i need to know if "help" event is really helpful - does it lead user to success. I've tried to do that in similar manner to previously discussed task - e.g. build a sequence : if attempt event is followed by help and help is followed by success then 1, else 0. However, i quickly realised that it is not working because there are some events in between(in updated sample i've called them "something_else").

                     

                    So i want to build a calculation that returns me user ID if user ever had combination of specific events and returns NULL if user did not. I prefer function to return user id instead 1 or TRUE because i think i will do distinctive count in of this column later on for my visualisation.

                     

                    I think i know how to do something like that in python but really struggling to do so in tableau.

                    • 7. Re: Getting value from previous row
                      Joe Oppelt

                      Take a look at the attached.

                       

                      Here I added up all the 1s an 0s for each Chat ID.  If greater than 0, then we met the condition somewhere.  Grab that chat ID.

                       

                      Notice that you get the value on each row.  It's a table calc, so it processes all the rows in the table.  Edit the table calc.  It looks like this:

                       

                      We have to set calc settings for all the table calcs involved in this equation.  There is a pulldown (circled) that lets you make sure all the calcs are processing in the order you need.  In the screen shot the original calc is set to what we had before.  Pull down the list and edit the new calc.  Notice that I didn't mess with "at the level" this time.  Settings for component calcs do not have to be identical when you have nested calcs.  I have some in my workbooks that have one calc with a complicated setting, another adding all those up across a row (TABLE(across),) and a third working within "cell".  There is no limit that I've encountered to nesting and setting different ordering.  It can get complicated, but you can do powerful things with this.

                       

                       

                      Anyway, now you can display the Chat IDs, filter with this calc, or whatever else you want.


                      Question for you:  ID 5.296 has a "fail" and a "start" with an intervening Event.  Would your business ever need to identify that?  It can be done, but if you don't need that, I'll drop the question.  (Your events are coming out alphabetically here.  Maybe this is just bogus data and I'm seeing something that really wouldn't happen in your real data.)

                      1 of 1 people found this helpful