12 Replies Latest reply on Nov 14, 2016 6:14 AM by Andrew Teer

# Forecasting with dynamic formating of labels (\$M vs \$K etc.)

Hi everybody,

I apologize if this is a rudimentary question, but I have not been able to find anything in the forums yet.  I am trying to do a full year forecast that has a dynamic label that will show in Millions or thousands or not whenever appropriate. I have found a trick that uses three calculated fields to accomplish this.  However, whenever I drag them onto label, the forecast disappears.

I have attached a workbook using Superstore that shows my basic issue.

Any thoughts?

Thanks in advance!

• ###### 1. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

Hi Andrew

Here is a solution.

Create three additional fields for the measure in this case I used Profit

[Profit(None)]

if sum([Profit])<1000 then sum([Profit]) end

[Profit (K)]

if sum([Profit])<1000000 and sum([Profit])>=1000 then sum([Profit]) end

[Profit (M)]

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.

==>

Thanks,

Shin

• ###### 2. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

BTW, attached file was TWB file, and it does not work to share.

Please attach packaged workbook (***.twbx) from next time.

Thanks,

Shin

• ###### 3. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

hey Shin,

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?

thanks!

• ###### 4. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

Please attach packaged workbook.

Shin

• ###### 5. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

HI Shin,

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.

• ###### 6. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

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.

Thanks,

Shin

• ###### 7. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

Hi guys,

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

"\$"#,##0;""

The ;"" string to the right effectively hides out negative values on a Label.

Please find the attached.

Yours,

Yuri

• ###### 8. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

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.

Yours,

Yuri

• ###### 9. Re: Forecasting with dynamic formating of labels (\$M vs \$K etc.)

Hey Yuri,

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!