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.
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!
Thank you so much Mercy! This was extremely helpful, I couldn't have done it myself!
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.
RunningTotalExcl1st9_1.twbx 19.6 KB
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"
if [CLASSIFICATION] = "B"
then [StartValue]+(RUNNING_SUM(SUM([Count of Asset ID1])) + [StartCountID])
then[StartValue]-(RUNNING_SUM(SUM([Count of Asset ID1]))- [StartCountID])
then [StartValue]-(RUNNING_SUM(SUM([Count of Asset ID1])) //// [Not sure about this - Is this what you want to do???]
1 of 1 people found this helpful
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!
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>
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
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!
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!