Here is a solution.
Create three additional fields for the measure in this case I used Profit
if sum([Profit])<1000 then sum([Profit]) end
if sum([Profit])<1000000 and sum([Profit])>=1000 then sum([Profit]) end
if sum([Profit]) >=1000000 then sum([Profit]) end
Then change the number format for each field.
Put all three to label. (Condition does not overlapped and one of three is always shown)
Remove line break.
Tableau_Sample_SS(2) SM _9.3.twbx 283.7 KB
BTW, attached file was TWB file, and it does not work to share.
Please attach packaged workbook (***.twbx) from next time.
thank you for the prompt reply! this is a great walk through for the trick that I mentioned in my original post. However, when i use this approach my forecast disappears from the Viz.
Any idea how to use this approach and maintain the forecasted numbers?
Please attach packaged workbook.
i have attached the twbx. Thanks for letting me know which type to use. You will notice the forecast doesn't not work on the second tab that uses the logic you mentioned.
Now I understand the issue, and unfortunately, considering the feature of "forecast", I don't think you can use this trick with "forecast" functions.
Pretty complicated calculation is on going background to show forecast as embedded functions, which multiple measures does not fit.
I don't use forecast often and did not realize this issue, sorry.
Trying to help here (albeit only partially).
The problem is divided into two parts.
The first is about No Forecast.
It is easy to see -- just go to
Forecast --> Forecast Options
and see the explanation at the bottom
of why Tableau couldn't be making it.
In this case there would be (some) problems
with (some of the) Measures on a view, namely:
-- There are NULLs in [$ (copy)] and [$k] for some Marks.
It is obvious giving the calculations itself.
-- Even if they not (by putting ELSE 0 before the END into the calcs),
there would be not enough data to make a Forecast.
Changing a Date pill aggregation could help sometimes,
for example getting from Years to Quarters would bring Forecast back.
The second problem is more subtle, and it is about
how to properly show Bar Labels when both pills are on Text.
There should be no NULLs, so initial calcs won't work.
The (partial) solution is to have negative values (instead of NULLs or Zeroes)
and custom format their pills on a view as
The ;"" string to the right effectively hides out negative values on a Label.
Please find the attached.
Forecasting issue_YF.twbx 1.2 MB
And one big caveat:
The results of the Forecasts may differ (significantly)
because of the Measure pills manipulations above.
Choosing the appropriate model is up to Tableau itself,
so the decision would be way different for each view.
As an example, one may set Year-Quarter on Columns
on both original (first) view, and my homemade Sheet 7 one
and compare the results and a model choosen (in Describe Forecast).
There is a 100% Seasonal one on the first view,
and a 100% Level one on my Sheet 7 view.
So apply with a discretion.
This is a very thorough explanation of what is effecting the calculation. However, with the dataset that I am using, I need the forecast to be true and not manipulated based on the negative numbers.
Thank you again for the great information around how the forecasting works!