12 Replies Latest reply on Apr 12, 2016 5:23 PM by Mercy Joseph

# Calculate difference for 2 columns

In my worksheet, I have 3 fields - one date, second (count of IDs) and the third one (Value).

What I want to achieve is - for 3/26/2016  the calculation field value should be (9387- 125) . I got this 125 from the ID field for 3/26, Similarly, for 3/27/2016  the calculation field should be (9262 -322) . I want to get values for other dates in a similar fashion. I am unable to do this on my own. Any help will be extremely appreciated. I really need this today for a meeting. If anyone could please help me. Thanks in advance!

Thanks.

• ###### 1. Re: Calculate difference for 2 columns

Hi Priyanka - thought I'd help out - here's a workbook with the field "EndValue" you probably are looking for. I created 2 parameters with the opening values from 3/26 and used the running_sum formula to keep track of your net value..Please see if it works.

Mercy

• ###### 2. Re: Calculate difference for 2 columns

Hi Mercy - thank you for helping me out! This file doesn't open on my laptop. I have tableau 9.1 version, is it possible for you to save it in this version? Thanks once again!

• ###### 3. Re: Calculate difference for 2 columns

Got yr msg - I redid it in 9.1 (as I'm not sure it can be saved directly into 9.1 from 9.2..)

1 of 1 people found this helpful
• ###### 4. Re: Calculate difference for 2 columns

Thank you so much Mercy! This was extremely helpful, I couldn't have done it myself!

• ###### 5. Re: Calculate difference for 2 columns

Hello Mercy,

You had helped me with this solution before, thanks once again! I need some help modifying this query. Now there is another field "Classification" that need to be considered while calculating the end value.

For example-  right now we have set startcountID to 19 but the count will now change as we need to consider classification field ('buy, sell, keep). End goal is to show 3 lines on classification on the graph. (Start Value parameter to remain the same)

I tried creating 3 startcountID parameters and use it in the endvalue query but that didn't work. Can you help me with this? Thanks a lot! let me know if you need further explanation.

Thanks again!

Priyanka

• ###### 6. Re: Calculate difference for 2 columns

Hi Priyanka - not sure exactly what you want to do - if you have an excel file of your values - I'll try and modify/replicate. I think I understand that you want EndValue to be incremented if the current row is "Buy" (or "B"),, else decremented if "Sell" (or "S"), or no impact if "Keep" ("K")?? But not sure how you want to deal with the opening count...

eg. will it work if you edit ENDVALUE calculation as follows:

if Index() = 1 then

if [CLASSIFICATION] = "B"

then [StartValue] + [Count of Asset ID1]

elseif [CLASSIFICATION] = "S"

then [StartValue]- [Count of Asset ID1]

elseif [CLASSIFICATION] = "K"

then [StartValue]

end

else

if [CLASSIFICATION] = "B"

then [StartValue]+(RUNNING_SUM(SUM([Count of Asset ID1])) + [StartCountID])

ELSEIF  [CLASSIFICATION]="S"

then[StartValue]-(RUNNING_SUM(SUM([Count of Asset ID1]))-            [StartCountID])

ELSEIF [CLASSIFICATION]="K"

then [StartValue]-(RUNNING_SUM(SUM([Count of Asset ID1]))   //// [Not sure about this - Is this what you want to do???]

END

END

• ###### 7. Re: Calculate difference for 2 columns

Thanks Mercy so much. This excel has the logic that I want to implement. I hope this excel helps you with the logic. Let me know. Thanks once again!

1 of 1 people found this helpful
• ###### 8. Re: Calculate difference for 2 columns

Attached is the file.Forgot to attach in my previous thread.

• ###### 9. Re: Calculate difference for 2 columns

Hi Priyanka - Wow! this was a BEAR!!! (At least for me! )

So I had to create a second Support Date field which set all NULL values to Jan 1, and then used a couple of nested LOD formulas for the StartValue and EndValue fields recognizing both Classification as well as Compliant fields in first (nested) round and then ignoring Compliant field in second (outer) round.

Please see if this works and let me knwo if you have any questions>

Thanks.

Mercy

• ###### 10. Re: Calculate difference for 2 columns

The new  workbook ("BuySellHold*.twbx") is attached in the message with the first workbook... as I couldn't find the attach workbook button when writing my recent response

• ###### 11. Re: Calculate difference for 2 columns

Thank you so much. This was very helpful indeed. However, the StartValue field needs a bit modification. I should have explained it more clearly earlier.  I want the first StartValue to be a constant for each of the classification values i.e.  Buy - (count of compliant= 'No' and classification='Buy' and Support date=All) and same thing for Hold and Sell.  Once we have that value, then use the CountID logic and do the running sum.

But, thank you once again for helping me out so much! I really got to learn new things with your help!

• ###### 12. Re: Calculate difference for 2 columns

Great! Hope you can manage modifying the STARTValue as needed - currently I think it calculates to a different fixed value for "Buy", "Sell", and "Hold" respectively based on Support-date being "Null" which is how it looked to be from your Excel file. Let me know if you need any further assistance. Glad you found it useful!