Plot monthly data from 2 different (unrelated) tables

[rywags11]

I am trying to create a visualization that shows actual dollars shipped by month as well as the budgetted dollars for each month overlaid on the actual dollars shipped.

My dollars shipped table is connected to 2 related ODBC sources and is pulling the date from one table (parent) and the actual dollars shipped from a measure within the other table (child) called SHIP_VALUE. (Parent = SHIPPER / Child = SHIPPER_LINE)

My budgetted dollars for each month is in a user built table that has a column for months and a column for budget (SHIPPING_PLAN)

I put the SHPPED_DATE field from the SHIPPER table into the "Shared axis" field, then put SHIP_VALUE in the "Column Values" field (measure from the SHIPPER_LINE table), and then the BUDGET field from the SHIPPING_PLAN table into the "Line Values" field. 

No matter what combination I've tried to move the summed fields to, I can only get ONE of the fields (SHIP_VALUE or SHIPPING_PLAN) to show correctly on the chart, but the other field sum's the WHOLE table it lives within. This seems to be tied to the date field, but how can I link the date columns in both tables if they are unrelated otherwise? 

Can anyone help me figure out how I can get 1 chart to show me the value of SHIP_VALUE each month and the changing budget value each month too?

Thanks in advance for any help

-Ryan

[MFelix]

Hi @rywags11,

You need to have a date table that makes the connection between these two tables and then use the dates on this tabel to make your visuals.

If you can share some sample of the data I can help you a little bit better.

Regards,

MFelix

[rywags11]

Thank you for the reply!

I hope I can put this together in the best way for you...

In the ODBC connected table SHIPPER, I have a bunch of fields I dont care about, but a field called SHIP_DATE

In the other ODBC connected table SHIPPER_LINE, I have the child relationship to the SHIPPER table; in the SHIPPER_LINE (child) table, I created a calculated column, which is written as written as "Ship Value = SHIPPER_LINE[SHIPPED_QTY]*SHIPPER_LINE[UNIT_PRICE]" so that I can calculate the total shipment value for each line item. I then created a measure called SHIPPED_QTY, which has the same formula but is used to sum the calculated column by the time period I established from the SHIP_DATE field in the SHIPPER table (parent). I can create any visualization from the date field in SHIPPER (parent) and the SHIPPED_QTY field fron my measure, with correct output on the visualization

I have a 3rd table called SHIPPING_PLAN, which I built by hand. It has a column called "Period" and one called "Budget". I typed the month into the Period column for each month in 2018, and then hand typed the planned budget for each associated period (month). It's important to know that the reason I'm having such a hard time is because the plan/budget value changes each month, so I cant have just a flat line going across the visualization, which Sum or Average is doing. 

Basically, if I put the date field from the SHIPPER table and the date field from the SHIPPING_PLAN table into the "Shared axis" field, it will calculate the table fields correctly for whichever date field is first/on top. The other one will be a total sum of all the data in the table. Below is a picture of what I'm seeing. The straight black line is the "Plan" field that needs to be moving up or down with it's associated values each month, and not just sum the whole table. I tried to include as much of the details as are necessary.

How would I make a data table that would connect the 2 tables? Would the connection allow it all to update automatically once it's established? 

Again, thank you for your help!

[MFelix]

Hi @rywags11,

If on all your tables you have a date column, and connect it to the Dimension Date table it will automaticly make the calculations if you place the date from the dimension table on the axis.

Based on what you are describing you only need to create a date column on the budget table that for now you only have period, just do the last day of every month and connecting this with the dimension date table should work together with the rest of the dates.

Regards,

MFelix