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

    Calculate difference for 2 columns

    Priyanka Agrawal

      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
          Mercy Joseph

          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
            Priyanka Agrawal

            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
              Mercy Joseph

              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
                Priyanka Agrawal

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

                • 5. Re: Calculate difference for 2 columns
                  Priyanka Agrawal

                  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
                    Mercy Joseph

                    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
                      Priyanka Agrawal

                      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
                        Priyanka Agrawal

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

                        • 9. Re: Calculate difference for 2 columns
                          Mercy Joseph

                          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
                            Mercy Joseph

                            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
                              Priyanka Agrawal

                              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
                                Mercy Joseph

                                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!