1 Reply Latest reply on Apr 25, 2012 8:58 AM by Tracy Rodgers

    Errors With Data Blending Calculation Using Dates

    Christopher Wheaton

      I am blending two data sets with Version 7.0. Both sets come from a SQL database, however they are housed in two tables that do not link well.

       

      The first data set (A) contains records of maintenance for an airplane. In the first data set I have multiple airplanes, and multiple records per airplane per day. In the second table (B) I have data for total airplane departures per day per airplane - so only one record per airplane per day.

       

      My relationships are as follows.

      Table A

      Table B

      Airplane ID

      Airplane ID

      YEAR(Maintenance Date)

      YEAR(Date)

      QUARTER(Maintenance Date)

      QUARTER(Date)

      MONTH(Maintenance Date)

      MONTH(Date)

      DAY(Maintenance Date)

      DAY(Date)

      Of course, I am using table A as my primary source.

       

      Using the two tables I am able to simply calculate a maintenance action per airplane departure rate.

      Here is my calculation method:  WINDOW_AVG(COUNT([Unique Maintenance ID]),0,0) / SUM([Table B].[Daily Departures]).

       

      The results are displayed using the line chart with each airplane rate displayed in separate lines (Airplane ID in Color shelf). DAY(Table A Maintenance Date) is on the Columns shelf, with the rate calculation on the Rows shelf.

      Note the: DAY() version on the shelf is MMM, DD, YYY, not day of month.

       

      After checking the math, the calculation initially works great. However as soon as I refresh the data (F5) or close the workbook and then re-open, the results from the calculation changes significantly from the actual maintenance per departure rate.

       

      Doing some back calculating, it looks like the new-incorrect number is calculated using the total maintenance actions, then dividing it by the total airplane departures for all time.

       

      I found I can correct the calculation error by editing (but not changing) my relationship settings. However it only seems to work with the DAY() relationship.

       

      To be more clear, here are the steps I take to temporarily fix the problem.

      Data -> Edit Relationships

      With Table A as my primary I select the DAY() relationship the select 'Edit'. When the Add/Edit Field Mapping window appears I make no changes to the presented relationships. Keeping the relationships just as they where when the window appeared I select the 'OK' button, then the 'OK' button on the Relationship window. Immediatly after, the software recalculates the data and the correct rate reappears.

       

      Again, if I do these steps on any other relationship - for example using the MONTH() relationship - the rates do not change from the erronious calculation mentioned earlier.

       

      Is there a way to prevent the need for this reset process?

       

      Thanks, Chris.