Apologies, I may not have caught the gist,
but it would be my impression that you may want to join your sources rather than blending them.
I made up one line of historical data and used the join criteria shown below.
Namely, I added one month to the historical date, and joined that to the forecast date.
Please see xlsx and twbx v10.3 attached in the Forum Thread:
thank you for your message. I'm currently working on joining the data tables, but since they're live and massive it's a time-consuming task.
Do you or someone else maybe have an answer to my calculation problem:
i.e. February Total Inventory = “Total Inventory” (in January) + “Inventory_forcasted to be shipping” (in February) + “Inventory_Forecasted to be in warehouse” (in February)
Hmm. The joining of massive data may be problematic.
Their might be way to limit the join ...
I'm not completely sure how much that problem will be alleviated by blending,
but I tried it anyway.
Attached is a workbook with two sources blended.
It uses the same general scheme: adding one month to the historical month,
and using that as a connector to the forecast month.
It would be my impression (but I could be wrong) that in order to add together
the three parts you desire, their values need to eventually end up all in the same row
in the composite datasource. That is want is attempting to be accomplished
by the join or the blend. Once that is done, then calculation should just be an
add (in the below case, it explicitly shows fields coming from the blend):
SUM([forecast (291297join)].[Inventory forcasted to be shipping])
SUM([forecast (291297join)].[Inventory Forecasted to be in warehouse])
But it is also possible that I have missed the boat entirely.
Please see workbook v10.3 in the Forum Thread:
291297forecast2.twbx 20.9 KB
No, you haven't missed the boat at all I see what you're going for and I think this will work for the case I outlined. Thank you!
Maybe you also have an idea for a case that builds on top of that one. I've been trying to build a calculated field that takes the last piece of historical information, which sits in said live database from which Tableau is pulling historical data - let's call it "Inventory (historic)". In an Excel Sheet I have assumptions on what might be in the warehouse and what will be on its way there ("Inventory_Forecasted to be in warehouse" and "Inventory_Forcasted to be shipping" respectively) - to stick with the previous example.
I'd like Tableau to pull the last month of what was actually the inventory and then add the assumptions from the Excel sheet - the calculation is basically the same as above (current Inventory + "Inventory_Forecasted to be in warehouse" + "Inventory_Forcasted to be shipping" , but Tableau won't produce an output for the forecasting period. For example, my last data set of historical data is December 2018, so Tableau will calculate January 2019 with last month's actual's plus my assumptions, but won't pull the calculation all the way to, here, June 2020.
The reason why I'm not simply adding them up, is because for looking at the remaining year 2019 and 2020, I need it to always look at the previous month and then add the assumptions for the future months.
Jan 2019 "Inventory Prediction" (category "A") = 153,526 ("Inventory (historic)") + 137,514 (forecasted to be in warehouse, Jan 2019) +21,386 (forecasted to be shipping, Jan 2019) = 312,426
February 2019 "Inventory Prediction" (category "A") = 312,426 (January 2019 "Inventory Prediction") + 137,281 (forecasted to be in warehouse, Feb 2019)+ 18.958 (forecasted to be shipping, Feb 2019)= 468,665 (which Tableau is currently not calculating).
I've created a quick sample workbook to showcase my problem.
Truly appreciate your input!
1 of 1 people found this helpful
Thanks for the workbook and detailed expected value.
I made a further attempt, not sure how well this can be adopted to your true setup.
My datasource has historic on one sheet, forecast on the other.
Sheets joined as described before. This time, there is only one instance
were the incremented HistoricDate = Forecast Date, so hopefully
it will cut down on the join time?
First I needed to unify the categories from the two sheets:
IF NOT(ISNULL([Category])) THEN [Category]
ELSE [Category (forecast)]
I think the key to the next part is to employ the PREVIOUS_VALUE() function.
IF ISNULL(ATTR([Category (forecast)])) THEN 0 //historic, not forecast so 0
ELSEIF ATTR([Date Hist])=WINDOW_MAX(MAX([Date Hist])) THEN //pull in the last historic
+SUM([Inventory Forecasted to be in warehouse])
+SUM([Inventory Forcasted to be shipping])
PREVIOUS_VALUE (0) //the zero only applies to first row, not really used here
+SUM(ZN([Inventory Forecasted to be in warehouse]))
+SUM(ZN([Inventory Forcasted to be shipping]))
It seems to give the first two forecast amounts.
Please see workbook v10.3 and datasource attached in the Forum Thread:
sorry for the delayed reply.
I just looked your workbook and at what you did there and it's actually EXACTLY what I need. This is so great. Thank you so much for your time and effort to come up with this suggestion!
Glad that it was helpful.
All the best.