5 Replies Latest reply on May 9, 2018 7:27 AM by Jim Dehner

    Previous_Value() returing String type

    emna benzarti

      Hi Community,

       

      I am facing a problem in creating a calculated field which on its rows takes into account previous rows of the same column.

       

      Here the type of data I’m analyzing:

       

      Level

      Action Name

      User (calculated field)

      1

      Visit of 'Hugh Jackman'

      Hugh Jackman

      2

      Loading of Page

      Hugh Jackman

      2

      click on a

      Hugh Jackman

      2

      click on  b

      Hugh Jackman

      2

      click on export

      Hugh Jackman

      1

      Visit of 'johnny Depp'

      Johnny Depp

      2

      Loading of Page

      Johnny Depp

      2

      click on a

      Johnny Depp

      2

      click on export

      Johnny Depp

       

      When field [Level] is equal to 1, I extract the Name of the user from field [Action Name] but when it’s a level 2 action,  I need to put in the calculated field the name of the user above.

      Column 3 is what I’m expecting.

       

      I tried this formula but it seems that PREVIOUS_VALUE() doesn’t work with Strings :

      IF [Level]=1 then SPLIT([Action Name],"'",2) elseif [Level]=2 then PREVIOUS_VALUE(0) END

       

      Do you have any Idea to solve this problem?

       

      Thank you in advance for your help.

        • 1. Re: Previous_Value() returing String type
          Jyothisree Rayagiri

          Hi

           

          Can you please share your workbook or data sample in excel format

           

          J

          • 2. Re: Previous_Value() returing String type
            pavan kumar

            Hi,

            split your formula.

            In one calculated field. - IF [Level]=1 then SPLIT([Action Name],"'",2) else " ";

            in another filed try window_max(calculated field).

             

            You should have different window for each record set .

             

            Regards,

            Pavan

             

            • 3. Re: Previous_Value() returing String type
              Jim Dehner

              good morning emna

               

              please see attached

               

              it will return this

               

               

              there are a couple things here - first I changed your formula slightly

               

               

              but the larger issue is with the data - there should be a field that groups the records for the entertainers together

              I added a Higher Level - see below

               

               

              without the higher level field all the records are independent

               

              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: Previous_Value() returing String type
                emna benzarti

                Hi,

                 

                Thank you for your answers,

                 

                Pavan,

                I tried your method but it’s returning “All fields must be aggregate …” as error in the second calculated field (using window_max) and I don’t understand how it is supposed to work without declaring a window.

                 

                Jim,

                I’m extracting a raw data and today, I’m still using an Excel formula to create the field Username. My aim is to integrate in Tableau this field without handling the Excel. But add a “higher level” requires to modify the Excel.

                 

                I tried another method, create a calculated field witch count the index since the last iteration of level = 1 and then use SPLIT(LOOKUP([Action Name],-[Rank]),"'",2) to get the username but I have the same error in my [rank] calculated field :

                [Rank]

                if [Level]=1 then 0 else 1+ PREVIOUS_VALUE(0) end

                “All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources”

                 

                In the second method, I am expecting to create columns 3 and 4 (or directly 4):

                 

                Level

                Action Name

                Rank

                Username

                1

                Visit of 'Hugh Jackman'

                0

                Hugh Jackman

                2

                Loading of Page

                1

                Hugh Jackman

                2

                click on a

                2

                Hugh Jackman

                2

                click on  b

                3

                Hugh Jackman

                2

                click on export

                4

                Hugh Jackman

                1

                Visit of 'Johnny Depp'

                0

                Johnny Depp

                2

                Loading of Page

                1

                Johnny Depp

                2

                click on a

                2

                Johnny Depp

                2

                click on export

                3

                Johnny Depp

                1

                Visit of 'Will Smith'

                0

                Will Smith

                2

                click on a

                1

                Will Smith

                2

                click on c

                2

                Will Smith

                2

                click on d

                3

                Will Smith

                2

                click on export

                4

                Will Smith

                2

                click on a

                5

                Will Smith

                2

                click on c

                6

                Will Smith

                2

                Loading of Page

                7

                Will Smith

                2

                click on export

                8

                Will Smith

                 

                Thank You

                 

                PS: I attached an Excel file as example and I’m using Tableau 9.3

                • 5. Re: Previous_Value() returing String type
                  Jim Dehner

                  Understand what your excel file is doing by position within the chart - excel is a cell based program which works from cell to cell -

                  Tableau is a data based system which works with dimensions and measures - in your file there is a dimension level that has 2 values 1 and 2

                  - all of the records with a level 1 are on the same level (i.e. independent and not related) and all of the records on level 2 are likewise independent and not related to each other AND they are not related to the records to the Level 1 records --

                   

                  if you can bring in any other field that the related level 1 records and level 2 records have in common then you can use that to tie them together -

                   

                  right now this is what your data looks like in the tableau data base

                   

                   

                  Jim