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

# Calculation on string value

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

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

Thanks ......

Currently it is showing  as below

 111 1 A 2 A 3 A3 0.09 4 Ba1 0.03 5 6.3  3.35

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

 111 1 A 2 A 3 A3 0.09 4 B1 0.03 5 2.95
• ###### 3. Re: Calculation on string value

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

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

Thanks guys..

it helped to understand but the problem did not resolve.

My data comes in like below

 1 A 10 1 B 11 1 A 15 1 B 20

`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.

• ###### 6. Re: Calculation on string value

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

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

• ###### 8. Re: Calculation on string value

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

• ###### 9. Re: Calculation on string value

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

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