13 Replies Latest reply on Mar 2, 2017 12:23 AM by . Ashish

# Why does the axis shows negative values while the Measure doesn't ?

Hi all,

I have a calculated field which sometimes return negative values. I want to filter the negatives so I adjusted the calculation to get rid of the 0.

If [] - [] < 0 then 0 else [] -[] end.

The problem is that if i bring this calculated field on the Rows shelf  along with a Dimension  , the axis still starts at a negative point.

My goal is to force the axis to start at 0 .

I know i can fixed the start point of the Axis but it also force me to fix the end point, which i don't want to do because it will grow in time.

Any clue on how to do this ?

thanks

Michel

• ###### 1. Re: Why does the axis shows negative values while the Measure doesn't ?

You're out of luck I believe: there's an idea here for what you want: http://community.tableau.com/ideas/1186

1 of 1 people found this helpful
• ###### 2. Re: Why does the axis shows negative values while the Measure doesn't ?

thanks for pointing to the idea , i added my vote.

Any idea  why the axis shows  negatives while the measure doesn`t ?

It look like the axis is built based on individual argument values inside the calculation  and not on the return values of the calculation.

• ###### 3. Re: Why does the axis shows negative values while the Measure doesn't ?

I am glad you voted up this one.

You might also like to vote up other similar ideas, see Axes idea collection.

Any idea  why the axis shows  negatives while the measure doesn`t ?

My guess is the axis makes room for eventual annotations.

Changing the formula from 0 to 20 removes the negative axis:

IF sum([Sales]) - sum([Profit]) < 0 then 20

else sum([Sales]) - sum([Profit]) end

• ###### 4. Re: Why does the axis shows negative values while the Measure doesn't ?

Thanks Johan,  i should browse the idea section more often , there is a bunch of ones pointing to limitations i often encounter.

Regarding your solution, unfortunately it doesn't apply to my situation.

First the 20 is not a magic number , it depends on the Filtering. The larger the range of the Dimension in the View the larger the number.  And it add false positive data.

thanks anyway,

• ###### 5. Re: Why does the axis shows negative values while the Measure doesn't ?

Michel, thanks for voting. I care for ideas and therefore happy when more participate.

I should have mentioned that the 20 formula was only mentioned to illustrate why the negative axis comes, and not intended as a real solution. I am sorry about that.

This formula is a more honest approach to solve your issue:

IF [Sales] - [Profit] <= 0 then null

else [Sales] - [Profit] end

• ###### 6. Re: Re: Why does the axis shows negative values while the Measure doesn't ?

no luck,   using null or 0 gives the same result

I though that it built the axis based on the possible results of [Sales] - [Profit]  whatever the IF is true or false. So i tried something like

IF  [Sales]  < [Profit]  then 0 else [Sales]  end     -     IF [Sales]  <  [Profit]  then 0 else  [Profit]  end

to get rid of the negatives before the calculation, but it gives the same result.

It's a bit annoying because in my real scenario , i have  a second axis representing a ratio with fixed values of  0 to 100% .  So if i don't want the ratio line to span in the negative zone of the other axis  i need to fix the starting point at  - 10%,  which doesn't make sense in this case. Anyway i'll live with that, the real problem is with the Data Source , i should never get more registrations than opportunities.

edit: i think you add the attachment after , i just saw it after posting this.  If you change the filtering (like removing everything from the filter shelf)  you get the problem back. I just put a tight filter in my exemple , to isolate a zone with a zero.

• ###### 7. Re: Why does the axis shows negative values while the Measure doesn't ?

I have a feeling that what Jonathan explained in Hide Filters (that don't filter underlying data) using table calculations to hide data in the view without filtering underlying data might work. See also view filter jtd edit.twbx.

• ###### 8. Re: Re: Re: Why does the axis shows negative values while the Measure doesn't ?

My understanding is that this has to do with the computed values of the marks in the view and how Tableau lays them out. When using the Line Mark Type, there are marks with a 0 value within an axis in the view, Tableau insists on extending the axis range into negative values. However, if there are no marks for an axis with a value of 0 and they are all positive, Tableau will automatically start drawing the axis at 0. This is true whether the pills on the opposite axis from the measure(s) of interest are discrete or continuous, BTW.

I'm pretty sure this doesn't have to do with the domain/range of measure values, or hidden data. I'm guessing that this is done to always show some context for the axis (otherwise, it's easy for lines to be near-invisible at 0), and even if you turn on the line markers they will get 1/2 cut off.

The workaround is to re-arrange your calc so that instead of making 0 the smallest value, the smallest value needs to be something bigger than 0. I've done some playing around with this in the past and set up a demo with your data, the smallest value depends on the vertical space available in the view. The minimum value necessary seems to be 0.005*the largest value, in a worksheet with less vertical space then that factor may need to double or get even larger.

Here's a way to set this up:

- Create a measure that makes the smallest value = .005 * the largest value, like:

IF [aggregate Measure] <= .005 * WINDOW_MAX([aggregate Measure]) THEN

.005*[aggregate Measure]

ELSE
[aggregate Measure]

END

- Use that as the measure for drawing the lines in the view.

- Turn off the tooltip for that measure

- Drag the real measure onto the Level of Detail Shelf (so that is what will show up in the tooltip).

- Optionally, set Format->Lines->Sheet->Zero Lines to None (so even if the view gets small and Tableau shifts the axis to include 0, there's not a line. In the case that the view is getting that small, the axis shift also be pretty small so the inaccuracy created might be acceptable).

See the "demo" worksheet in the attached.

Jonathan

1 of 1 people found this helpful
• ###### 9. Re: Re: Re: Why does the axis shows negative values while the Measure doesn't ?

It worked perfectly. With my range of values i add to change the .005 for .01,  but i prefer to have the line sligthly hovering over the Zero line than displaying a negative zone.

Thanks alot !

Michel

• ###### 11. Re: Why does the axis shows negative values while the Measure doesn't ?

You're welcome!

• ###### 12. Re: Why does the axis shows negative values while the Measure doesn't ?

Depending on the filtering, my WINDOW_MAX can go from 10 to 100K  so it was not possible to find a 'one size fits all'  multiplicator.  Here is my latest testing... so far so good.

Hopefully a futur version will allow to do this with one click of a mouse.  Fix Start: 0

IF WINDOW_MAX([aggregate Measure]) <= 10 THEN

IF  [aggregate Measure] <= WINDOW_MAX([aggregate Measure]) *.01 THEN WINDOW_MAX([aggregate Measure]) * .01

ELSE [aggregate Measure] END

ELSEIF WINDOW_MAX([aggregate Measure]) <= 100 THEN

IF  [aggregate Measure] <= WINDOW_MAX([aggregate Measure]) *.015 THEN WINDOW_MAX([aggregate Measure]) * .015

ELSE [aggregate Measure] END

ELSEIF WINDOW_MAX([aggregate Measure]) <= 1000 THEN

IF  [aggregate Measure] <= WINDOW_MAX([aggregate Measure]) *.02 THEN WINDOW_MAX([aggregate Measure]) * .02

ELSE [aggregate Measure] END

ELSE

IF  [aggregate Measure] <= WINDOW_MAX([aggregate Measure]) *.025 THEN WINDOW_MAX([aggregate Measure]) * .025

ELSE  [aggregate Measure] END

END

• ###### 13. Re: Why does the axis shows negative values while the Measure doesn't ?

Thank you Jonathan again! Even in case of background images I was facing the same issue of

1. Not being able to fix both x and y-axes. Even fixing them to start at 0 and end at the image size, either of the axes would get distorted to show different range. Thus not displaying the entire image full screen, even with Entire view option.

2. So, most of the time the x-axis would show negative range with blank portion and image from 0,0.

I fixed this issue by fixing both the axes ranges to start from 1 and voila!!!

Regards,

Ashish