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



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




          • 2. Re: Calculation on string value
            Abhisek Sarkar

            Thanks ......


            Currently it is showing  as below


            5 6.3  3.35


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


            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])



                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


                  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.




                  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.