9 Replies Latest reply on Jun 29, 2016 8:07 AM by John Sobczak

    Trouble Linking Budget vs. Actuals Excel Tables

    Sean Trageser


      I have two separate excel tables I am trying to link to Tableau to track overtime usage. One which contains budgeted OT hours and another with actual OT hours. All of the fields are the same in both tables. My fields on both tables include the following: Location, Date of Event, Quarter of Event, Fiscal Period of Event, Month of Event (with is necessary due to weird pay periods), time reporting code (various labor codes), quantity of hours and expense accounting code. As an example, here is what one budgeted row looks like:

       

      Location        Date           Quarter             Period     Month    TRC        Quantity   Account

      20131101     7/1/2016     First Quarter     Per 1       July       OT2            1.466     501070      

       

      My actuals table contains less data so I am using that as my primary data source. I am using an inner join with budget table and am joining in the following order - location, account, and report date. The issue comes up when I go to my Tableau worksheet. When I look at my July data for example if I have 3 events with "actuals" data, Tableau is multiplying the number of events by the budgeted quantity. For example, I have 3 different employees with overtime on July 1. My budgeted OT for July 1 was 1.466 hours. Instead of the Tableau workbook showing my budgeted amount of 1.466 for that day, it is showing it as (1.466*3), or 4.398. This is my first problem.

       

      My second problem is Tableau is only calculating the budgeted overtime for the days on which there was corresponding "actuals" overtime. For example, for the period of July, I should see 23 days with 1.466 hours of OT budgeted, or 33.718 total OT hours budgeted for the July period. Instead I am only seeing the budgeted OT for July 1 (the only day with an occurrence).

       

      Any insight on how these two issues can be fixed would be greatly appreciated.