10 Replies Latest reply on Mar 17, 2017 2:16 AM by Abhisek Sarkar

    Calculation on string value

    Abhisek Sarkar

      Can someone please suggest an way to do the below.

       

      1. One column has value a well as multiple number.

      2. I need to show the text value and subtraction of multiple values if present.

      3. I am able to show the text value as well as number but if there are multiple values then it is populating in the same cell.

       

      please check the attached as a sample file.

        • 1. Re: Calculation on string value
          Rendy Jansen

          Hi,

           

          Could you provide and example of how you want it to look like? I'm not sure what you're want to achieve.

           

           

          Cheers,

          • 2. Re: Calculation on string value
            Abhisek Sarkar

            Thanks ......

             

            Currently it is showing  as below

             

            1111A
            2A
            3A30.09
            4Ba10.03
            5 6.3  3.35

             

            But i would like change the view to below where the bold part is the difference.

             

            1111A
            2A
            3A30.09
            4B10.03
            5      2.95
            • 3. Re: Calculation on string value
              Joe Oppelt

              When I opened your workbook my version of Tableau upgraded it so you're not going to be able to see what I did.


              Always identify your tableau version so that people can open your workbook in the right version.

               

              So I created a calc.  I called it [Value Number].

               

               

              if [Flag] = "V" then float([Value Text]) END

               

              Now you have an actual measure that you can do things with.

               

              If I were working with your data set I would shape this data source differently.  You have no measures.  It's very difficult to do good analysis with no measures.

               

              So once I created the measure calc, I dragged it to TEXT.  Now your sheet looks like this:

               

               

              You can do your math with the measure now.

              • 4. Re: Calculation on string value
                Rendy Jansen

                You can also try creating a Calculated Field, with the following formula:

                 

                IF ISNULL(SUM(IF [Name] = "A" THEN [Value] ELSE NULL END) -

                SUM(ZN(IF [Name] = "B" then [Value] ELSE NULL END))) = FALSE THEN

                str(SUM(IF [Name] = "A" THEN [Value] ELSE NULL END)-SUM(ZN(IF [Name] = "B" THEN

                [Value] ELSE NULL END)))

                ELSE attr([Value Text])

                END

                 

                And replace [Value Text] in your dashboard with it.

                 

                Does this help?

                • 5. Re: Calculation on string value
                  Abhisek Sarkar

                  Thanks guys..

                   

                  it helped to understand but the problem did not resolve.

                   

                  My data comes in like below

                  1A10
                  1B11
                  1A15
                  1B20


                  And when i am showing it in tableau it shows like below,i
                  need to calculate the difference also in a separate column. the main problem i am facing is to show the difference in the same row.

                   

                  ABResult
                  11011(11-10)
                  1520(20-15)

                   

                  Highly appreciated guys if you can help on this.you can also find this type of scenario in below stackoverflow link.

                   

                  Subtract fields of a column - Tableau - Stack Overflow

                  • 6. Re: Calculation on string value
                    Joe Oppelt

                    It's time to post a sample workbook.  Let me know your tableau version when you upload the workbook.

                    • 7. Re: Calculation on string value
                      Joe Oppelt

                      My bad...  What version is the workbook you uploaded?  I'll show you what I was describing.

                      • 8. Re: Calculation on string value
                        Abhisek Sarkar

                        oh sorry i forgot to mention that. i was using version 10.0

                        • 9. Re: Calculation on string value
                          Joe Oppelt

                          See attached.

                           

                          Sheet 1 is your original sheet.

                           

                          Sheet 2 has the first of my modifications.

                           

                          First of all, I did some re-shaping of your data.  You can do away with [Value Text] if you do what I simulated here.  I made [S Value] to be a new dimension that holds those values.  And then your [Value] field already contains those numbers for A and B as numeric data.  And given that numeric data, you can do your math on stuff that already exists.  See [Calculation 1].  This calc says to take the current value and LOOKUP what came before it.  In the case of Name="A" values, there is nothing before it, so it generates null.  For "B" values the LOOKUP grabs the "A" value and subtracts.  (Wrapping the second half in ZN() loads a zero if the "B" value is null, (as is the case for Sub-ID = 3 or 4.)  So for sheet 2 I display this calculated value along with the actual [Value].  For the sake of demonstration I labeled the numbers on the display here.

                           

                          Sheet 2A removes the labels and the calc so that I can display it the way I want on the dashboard.

                           

                          Sheet 3 displays the Calc by itself.  Because Tableau works in a dimensional grid, there isn't a column under [Name] to shove the calc value.  So I shaped a separate sheet to display the calc values by themselves.


                          Look at Dashboard 1.  Here I have positioned the two sheets side-by-side so that it looks like one report to the user.

                          1 of 1 people found this helpful
                          • 10. Re: Calculation on string value
                            Abhisek Sarkar

                            Thanks guys !!!!!!!!!!! it really helps me a lot.