4 Replies Latest reply on Aug 18, 2016 7:33 AM by Noah Bakst

    Issue displaying revenue/spend data from connected sources

    Noah Bakst

      Hi all,

       

      Sorry if this question is highly rudimentary, but it's been baffling me for a few weeks...

       

      I have two connected sheets. The first one is a revenue report with six columns: Date, Office Location, zip, Customer ID, Referral Source, and Revenue. The second is  a budget spend report with multiple headers: Date, Office, Office Code, Publication, Short code, Impressions, Primary zip, Budget, and date.

       

      The sheets have the following relationships:

       

      Revenue Report
      Budget Report
      DateDate
      Office LocationOffice Code
      Referral SourceShort Code
      ZipZip

       

       

      The connections are correct, but the issue comes when I link the referral source to the short code.

       

      When the budget report is the primary document and referral source is linked, individual stats are correct but the revenue doesn't line up.

       

      I believe this is because some budgeted short codes do not have a revenue referral source associated with them and thus the code does not exist anywhere in the revenue report. There are 103 referral source codes that have no corresponding code in the budget report and 7 codes in the budget report that do not exist in the revenue report. While there isn't much revenue associated with this data, they still need to be accounted for. The data sets share 12 referral source/short codes that make up a majority of the revenue.

       

      I've tried using both documents as the main data source. If i use the revenue report as the filter, I can only see months where there is a return. For example, we spend $1k on PR every month this year, but only saw a return in January and April. I can only see the data from January/April when the data is connected. I believe this is because a short code/referral source "PR" exists in both data sets. Ideally, I want to filter by referral source or short code and the worksheet would show all the month's budget and return, even if there is no return.

       

      Is it possible to link these documents and create this short of relationship?

       

      Thanks,

       

      Noah