Hello all. I am attempting to plot the shipments my company makes by plotting the planned vs actual $ shipped. Basically, each row in the data contains the "Promised ship date", the "Actual ship date", and the value ($) of this shipment. I need a line that represents the actual $ shipped by date (from the "Actual ship date") on the same graph as a line that represents the $ that should have shipped (from the
"Promised ship date"). These are cumulative by and filtered to include only the current month's data. I have tried to think about how to accomplish this with calculated fields but am struggling to figure it out. Thanks for any help.
Ok, I attached an extract of the workbook, if you need more just let me know. The "Date Required" and "Date Shipped" columns are the dates I need to use and the "IV_SUB_TOTAL" is the $ amount that I am graphing. Thanks for any further help, it is much appreciated!
Message was edited by: Michael McAlpin
UPDATE: I think I have found the solution to my issue, it is located in the "Sales Dashboard Extract New" attachment below. I am going to look elsewhere in our database to find the dates/data that extend into the future. Here is what I was able to do though as a solution to my original problem. I just used dual and synchronize axis to do this, and added a "This Month" filter onto the [Dater Required] field. This was the best solution possible with the data I have given you guys I think. I'm going to wait on marking this complete until at least tomorrow so I have time to make sure I'm not missing anything in the data. Until then, thanks guys! I love this community!
UPDATE: Ok guys, I think I'm finally getting there but it took some very complicated manipulation of the data in order to do it. I had to link in the MOC data table from which I multiplied the [MOC_PRICE] by [RQ_QTY_REQ] to get the [Estimated Ship $] calculated field. I had to do this calculation in another data connection though due to repeat values in the original data source. I then graphed all 4 fields on the same chart with [Date Shipped] and [Date Required] as columns and [IV_SUB_TOTAL] and [Est. Ship $] as rows with a "This Month" filter on date required and a conditional filter on [Date Shipped] ( isnull([Date Shipped]) or month([Date Shipped])=month(today()) ) so it wouldn't filter out future dates. With double dual and synchronized axises this was what I got. My issue is that 2 of the lines are correct and 2 are useless but I have no way of distinguishing/getting rid of the unimportant lines (the [Date Shipped]/[Est. Ship $] and [Date Required]/[IV_SUB_TOTAL]) . If I should put this in a new question on the forum since it is getting so long and mark this one correct, tell me. The newest extract is below.