4 Replies Latest reply on Apr 27, 2016 7:28 AM by Morgan Aguiar

    How to Manipulate Data and Totals in Tableau

    Morgan Aguiar

      Hi Tableau Community,

       

      I've been using Tableau for about two years now and I've been trying to create a new process using Tableau which will allow me to manipulate my imported data (Specifically a String Type).

       

      Example:

       

       

      Column1 Column2

      R   (ABC)   1A3

      O   (BCD)   2B4

      W   (EFG)   4B6

      1    (EFG)    NULL

       

       

      If I wanted the NULL to equal 4B6 (the previous cell value filtered by the primary key), note that there are multiple NULL values and I would like to replace the NULL with the string of its previous value.

       

      Column1 Column2

      R   (ABC)   1A3

      O   (BCD)   2B4

      W   (EFG)   4B6

      1    (EFG)    4B6

       

      I've looked into PREVIOUS type calculations and REPLACE but I've been having issues due to the string type format.

       

      Any help would be appreciated.


      Thank you.

       

      M.A

        • 1. Re: How to Manipulate Data and Totals in Tableau
          Pamela Germain

          I think previous_value might work if you use attr(), something like ifnull(attr([column2]), previous_value(attr([column2])))

          You could also do a lookup, something like ifnull(attr([column2]), lookup(attr([column2]),-1)).

          • 2. Re: How to Manipulate Data and Totals in Tableau
            Morgan Aguiar

            Pamela Germain, Thank you for the quick response and starting steps.

             

            After using the previous_value formula I started obtaining values as an (*) asterisk, once I switch the Attr to min / max I would see a value. The reason behind this is that some values have multiple values / nulls when referred back to column 1...

             

            Example (replicating your graph above):

             

            Col1      Col2    Calculation1

            (ABC)    1A3     1A3

            (BCD)    2B4     2B4    

            (EFG)    4B6     4B6

                          Null     4B6

            (GHI)    4B6      4B6

                         4D6     4D6

                         4H6     4H6

                          Null     (*) ... If min or max is used it will yield a value (min or max of the values within intersections Col1 -> Col2)

            (LMO)   Null     (*)

                         Null     (*)

             

            How would I make sure if both values in the intersection Col1 -> Col2 = Null then replace with a value 0 instead of previous value ? (in some cases I'm taking the value from the above intersection and not its own intersection).

             

            Also would it be possible to take a percentage of the null's measure (based on all measure values under one intersection) and allocate it towards other non-null value measures within the same intersection ?

             

            From:

            Col1      Col2    Calculation1  Measure

            (GHI)    4B6      4B6               500

                         4D6     4D6               300

                         4H6     4H6               200

                          Null     (*)                 1000

            Grand Total                           2000

             

            To:

            Col1      Col2    Calculation1  Measure

            (GHI)    4B6      4B6               500 (%50 of 1000 allocated) = 1000

                         4D6     4D6               300 (%30 of 1000 allocated) =  600

                         4H6     4H6               200 (%20 of 1000 allocated) =  400

            Grand Total                                                                            2000                          

             

            Thank you in advance.

             

            Regards,

             

            M.A

            • 3. Re: How to Manipulate Data and Totals in Tableau
              Pamela Germain

              Ah, my apologies, it was an assumption on my part that you would not have multiple values resulting in an asterisk. Attr() will not work then and unfortunately when I run into these type of situations I normally re-work my data with a SQL query so that it will work with Tableau. I think you need someone with more in-depth Tableau data manipulating knowledge to help if you don't have the option to stage/prep the data before you get it in Tableau. Best of luck.

              • 4. Re: How to Manipulate Data and Totals in Tableau
                Morgan Aguiar

                Thank you Pamela. Yes in the past I was able to manipulate the data from the source to solve these types of issues, unfortunately for this case I do not have access to the source data and can only transform the data (basic ETL) once it has been loaded into Tableau.

                 

                It was just my assumption that there might be some techniques out there that I was unaware of that could help me accomplish these requirements.

                 

                Regards,

                 

                M.A