9 Replies Latest reply on Oct 18, 2016 1:17 PM by Lisa Li

# Sum Data from two tables based on Month

Hello Experts,

I am new to the world of Tableau and so far enjoying the capabilities. I am working on a Dashboard and stuck with an issue. The question might be simple with a simple solution however I am not able to figure out how to get this done. So, requesting your help.

I have two tables as below: One has the actuals as of date and the other have the forecast

MonthActuals

Jan

6
Feb15
Mar15
Apr16
May16
Jun17
Jul18
Aug19
Sep20

Second Table with Forecast:

MonthForecast
Sep1
Oct2
Nov1
Dec3

Using the above two tables I would like to create the chart similar to the below:

I am not able to create a calculated field to get the sum of actuals and forecast. Can you please advise me on how I can achieve this.

Thank you for all your support.

• ###### 1. Re: Sum Data from two tables based on Month

Hey Nataraj,

Assuming you are using the same Date dimension for both of those, maybe you can try this calculated field or whatever date is the max of data available for Actual values:

if [Date] < today() then [Actual]

else [Forecast]

end

You can also check out this link: http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.html#forecast_create.html

-Lisa

• ###### 2. Re: Sum Data from two tables based on Month

Hi Lisa,

During the month of September, We need to sum up the Actuals + Forecast. When I tried to pull Forecast into the formula it comes in as a ATTR  and sums up to only 0. Can you please advise on how we can fix it.

Also, if I would need to sum it up month on month based on the baseline. Can we use a formula to do it? Please advise.

Thank you!

Nataraj

• ###### 3. Re: Sum Data from two tables based on Month

Hmm, would it work if you had this:

if datepart('month',[Date]) = 9 then [Actuals]+[Forecast]

elseif datepart('month',[Date])< 9 then [Actuals]

else [Forecast]

end

And instead of 9, you can replace that with datepart('month',today())-1 if all you want is the last month from today.

If that doesn't work, would you be able to share a workbook with markup data?

-Lisa

• ###### 4. Re: Sum Data from two tables based on Month

Hi Lisa,

As suggested, I have created a sample workbook. I have Forecast vs Actuals in the worksheet.

I am unable to attach the file to the message hence used an external URL.

Now I would like to add a dotted line from the end of actuals till year end to show the pipeline which can be compared against the Forecast. Can you please advise.

• ###### 5. Re: Sum Data from two tables based on Month

With the calculations I described earlier I was able to create this chart:

Is that what you want?

For future reference, here is a link explaining how to attach files to your message:Why don't I have the option to attach content to a Reply? Where's the Correct Answer button?

-Lisa

CoEnterprise | Home

• ###### 6. Re: Sum Data from two tables based on Month

Hi Liza,

This exactly what I was trying to achieve. However I am not able to replicate the same. Would it be possible for you to share a sample workbook please.

Thank you!

Nataraj

• ###### 7. Re: Sum Data from two tables based on Month

Hey Nataraj,

Sorry! I didn't realize I didn't reply. Was everything okay? Does it work now?

-Lisa

• ###### 8. Re: Sum Data from two tables based on Month

Hi Liza,

Yes, I was able to resolve it. Thank you for all your help.

Nataraj