6 Replies Latest reply on Sep 21, 2016 2:35 PM by David Li

Is it possible to use a measure without using its axis? (attached wkbk)

Lionel Yu Sep 21, 2016 2:07 PM

This is an odd request but I can't figure out how to achieve this in Tableau. I'd like to show the cumulative % change of positive and negative values on the same line. In the attached wkbk I have a time series of % change of 10 days, but I want to show the cumulative % change at the end. Due to this being a table calculation, I have to use "Day" as the row and a dimension "Positive Negative Return" as the column. However, when this happens the negative final number is slightly below the final positive number, as the last negative % was on day 9 and the last positive % was on day 10. my goal here is to try to get these two numbers on the same level

is this possible to achieve? Thanks in advance for any help or thoughts.

example workbook 24.twbx 13.3 KB

1. Re: Is it possible to use a measure without using its axis? (attached wkbk)

David Li Sep 21, 2016 2:14 PM (in response to Lionel Yu)

Hi Lionel! I think the easiest way to do this is to set the axis range to be enormous, let's say -10,000 to +10,000. Since the axis is blown out, the two numbers will look like they're colinear.
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Is it possible to use a measure without using its axis? (attached wkbk)

Lionel Yu Sep 21, 2016 2:17 PM (in response to David Li)

That's actually really funny and it worked! Thank you. I'm still open to a more elegant solution but this was a great idea and did the trick.
Like Show 0 Likes(0)

Actions
3. Re: Is it possible to use a measure without using its axis? (attached wkbk)

Vincent Baumel Sep 21, 2016 2:20 PM (in response to David Li)

This is a really clever solution. Nice work, David!
Like Show 0 Likes(0)

Actions
4. Re: Is it possible to use a measure without using its axis? (attached wkbk)

David Li Sep 21, 2016 2:26 PM (in response to Lionel Yu)
Thanks, guys!

Regarding more elegant solutions, we can also show this in table form to alleviate the need to do the tricky trick.
Let me explain how I got here. First, I converted Day to be discrete. Then, I changed the final return calc to this:
(1+ZN(SUM([% change])))*(1+PREVIOUS_VALUE(0))-1
Note that the argument in PREVIOUS_VALUE() actually provides the value that the function will return when it's at the first index. So if you put 0 there, when you're at INDEX() = 1, it'll return 0.

I also wrapped the SUM([% change]) in ZN() to make it 0 if NULL.

Then, I just used the LAST() trick you were using before, except that I embedded 0 directly into it so it became a Boolean.

example workbook 24.twbx 13.9 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: Is it possible to use a measure without using its axis? (attached wkbk)

Lionel Yu Sep 21, 2016 2:34 PM (in response to David Li)

David you're a genius thank you so much!!!!
Like Show 0 Likes(0)

Actions
6. Re: Is it possible to use a measure without using its axis? (attached wkbk)

David Li Sep 21, 2016 2:35 PM (in response to Lionel Yu)

Thanks! And you're very welcome!
Like Show 0 Likes(0)

Actions