1 Reply Latest reply on Jun 25, 2018 6:06 PM by swaroop.gantela

    Matching Dates across two excel tabs

    Ashley Dettlaff

      I have created a sample data workbook to mimic the problem I am having.

       

      I have estimated mail volume numbers that are labeled with the month (for April just 04/01/2018) and the day (Mon-Sat) and these estimates would be true for any Monday in April whether it is the 1st or the 8th, etc.

      There is actual mailed volume data for each specific day in another tab of the excel sheet.

       

      I want to plot the specific day by day volume, and I want to add cell reference bars that will show what the planned volume was expected as for that specific date.

      Planned volumes only have month and weekday, so I tried to use the logic:

      Day and Month for the actual volume data were made using DATENAME and DATETRUNC, so they should match the planned/estimated data

       

      I believe the ATTR function is setting my values to an *

       

      Any help to solving this problem would be much appreciated

        • 1. Re: Matching Dates across two excel tabs
          swaroop.gantela

          Ashley,

           

          I don't think it will be necessary to create a calculated field to plot the join,

          one can plot the plan directly, if the two datasets are blended or joined together.

           

          In the workbook attached in the Forum Thread, an example of a blend and a join are given.

           

          For the blend, I created a Custom Date of Month Value (using Custom Dates )

          in each of the datasources, one on [Date] and one on [Month].

          I used this to blend the two datasources together in Data>Edit Relationships,

          along with [Day] and [Type] (please see screenshot below).

          Then, you can just plot SUM(Plan) on the second axis.

           

          This can also be accomplished with a join of the two datasources,

          as shown in the second screen shot.

           

          274032match.png

           

          274032matchJoin.png