13 Replies Latest reply on Jun 18, 2014 5:16 AM by Michael McAlpin

    Plotting the Planned vs Actual $ Shipped by date

    Michael McAlpin

      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.

       

      shipments chart.PNG

      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.

        • 1. Re: Plotting the Planned vs Actual $ Shipped by date
          Sourabh Dasgupta

          Hello Michael, would it be possible for you to share a sample data set and a screenshot of the desired output.

          1 of 1 people found this helpful
          • 2. Re: Plotting the Planned vs Actual $ Shipped by date
            Michael McAlpin

            I posted the things you requested, hopefully I did it right.  This is my first time posting data like that.

            1 of 1 people found this helpful
            • 3. Re: Plotting the Planned vs Actual $ Shipped by date
              Matt Lutton

              Post as a packaged workbook.  Extract your data, if needed, and File>>Save as>>TWBX

              1 of 1 people found this helpful
              • 4. Re: Plotting the Planned vs Actual $ Shipped by date
                Michael McAlpin

                Thanks, sorry for the confusion, the file is up now.

                1 of 1 people found this helpful
                • 5. Re: Plotting the Planned vs Actual $ Shipped by date
                  Matt Lutton

                  I'm not exactly sure what you're looking for here, and there are a lot of sheets in that workbook.  Can you trim it down to focus on one particular area (maybe remove/hide any unneeded fields for this problem) and clarify the goal?

                   

                  Do you have a way to identify plan versus actual IV_SUB_TOTAL, or is that what you're asking how to do?  This would be really simply if you had a way to distinguish between "actual" and "plan" via a dimension in the data (meaning one row for each "planned" amount and another for the "actual".

                   

                  But I may be missing the overall goal. 

                  1 of 1 people found this helpful
                  • 6. Re: Plotting the Planned vs Actual $ Shipped by date
                    Michael McAlpin

                    Basically the actual and planned date are built into the same line of data. The IV_SUB_TOTAL is the same value for both the shipped date and required date.  I want to plot the sum of this value over the course of a month according to the required date and the ship date on the same axis.  As I continue to think about it though, I am not even sure that this is possible.

                    1 of 1 people found this helpful
                    • 7. Re: Plotting the Planned vs Actual $ Shipped by date
                      Joe Oppelt

                      I can think of two things you might want to try.


                      First, I am assuming you can plot each one individually.  If you aren't even there, um ... Ignore the following.

                       

                      Put the two measures on your ROWS shelf.  You'll get side-by-side graphs.  Then right click on the second measure and selecr DUAL AXIS.  That will place the two graphs on the same real estate.

                       

                      Similarly, (and especially if you have more than two graph lines you want to see), you can drag MEASURE VALUES to the rows shelf, and MEASURE NAMES to the data shelf (actually, plunk it in th COLORS shelf so each line gets a different color) and drag MEASURE NAMES to the filters shelf and click on the actual measures/calcs you want to see graphed.

                      1 of 1 people found this helpful
                      • 8. Re: Plotting the Planned vs Actual $ Shipped by date
                        Matt Lutton

                        You can certainly use a dual axis to display running total of SUM(IV_SUB_TOTAL) for Date Required and Date Shipped, but the results won't look anything like your mockup since that won't represent actual vs planned:Running Total.png

                        So again, I think I'm missing the overall goal here.

                        1 of 1 people found this helpful
                        • 9. Re: Plotting the Planned vs Actual $ Shipped by date
                          Joe Oppelt

                          Let me see if I can restate your problem:

                           

                          You want to collect a running sum of all your rows based on one date value in each row.


                          Then you want to collect a running sum of all your rows based on a second date value in each row.  (Complicating this is the fact that the ACTUAL dates will be null from some date -- tomorrow -- forward, but PLAN dates can stretch into the future.  Am I correct on that?)

                           

                          Then you want to plot the two running sums along a common date axis.

                          • 10. Re: Plotting the Planned vs Actual $ Shipped by date
                            Don Barnetson

                            Hi Michael,

                             

                            I think that this is closer to what you are looking for. I'm not 100% if I've selected the correct measures to create the lines, as I don't know how you look at your data, but it's could be a start for you.

                             

                            I've created 2 calculated fields. 'Calculation1' is showing the shipping date $ value (you may need to change the date dimension) using this formula:

                             

                            if [SH_DATE_REQ]=[Date Shipped] then [IV_SUB_TOTAL] end

                             

                            'Calculation2' is showing the required date $ value (using IV_SUB_TOTAL):

                             

                            if [RQ_DATE_PROM]=[Date Required] then [IV_SUB_TOTAL] end

                             

                            I've also created a duplicate of both '[IV_SUB_TOTAL]' and 'Calculation1' to create the square markers on the ship date line.

                            Now for making the graph...place both 'Calculation1' and 'Calculation2' on the rows shelf, then grab the top left corner of your second measure on the graph and place it on to of your first measures graph. This will combine the 2 line graphs.

                             

                            Now you can place your 'Calculation1 (Copy)' on the rows shelf, change the marks to square and then select dual axis.

                             

                            You'll have to change the colors, sync the 2 values, etc....but that should give you something close to what you are looking for.

                             

                            Hoppe this helps,

                            Don

                            1 of 1 people found this helpful
                            • 11. Re: Plotting the Planned vs Actual $ Shipped by date
                              Michael McAlpin

                              This looks pretty good but the numbers are way low due to not all the values being included in the final graph, as the "if" statements exclude a lot of them. The [RQ_DATE_PROM] is not a very important piece of data to me and most likely shouldn't be involved in this calculation.  I did post an update that I think is the best possible solution to this issue that I found.  Thanks for the help though.

                              1 of 1 people found this helpful
                              • 12. Re: Plotting the Planned vs Actual $ Shipped by date
                                Don Barnetson

                                I'll take another crack at it this morning if I get a few minutes.

                                1 of 1 people found this helpful
                                • 13. Re: Plotting the Planned vs Actual $ Shipped by date
                                  Michael McAlpin

                                  That is exactly correct. I actually did make quite a bit more progress though towards solving this issue but it seems to be through the use of a different data field.  I explain in an update to the original question, but am wondering if this has veered too far off track and should start a new question considering the original question's info is not fully relevant any more.  Thanks for the help.

                                  Mike

                                  1 of 1 people found this helpful