2 Replies Latest reply on Feb 14, 2017 12:34 PM by Maddy Pena

    Trouble Combining Actual & Budget Data (Different sheets)

    Maddy Pena

      Hello guys!

       

      I'm trying to create a dashboard that shows the actual vs budget in a crosstable, something like this:

       

       

      Where the actual values are shown on the corresponding month and the rest of the year shows the budget values. I have all the concepts listed and the budget values from Jan-14 to Dec-17 in one sheet. To create a Budget measure, I created a pivot on these values.

       

      But when I want to create a join with the actual data (of which I only have Jan-17 values), the data is multiplied along the all time period of the budget data:

       

       

      I'm fairly new at these type of dashboards, and I don't know how combine the data, and I'm not sure how to work with dates, as I need both measures, but the detail of one of them is by day, and the other is by month.

       

      Any tips would be appreciated. Thanks!

        • 1. Re: Trouble Combining Actual & Budget Data (Different sheets)
          Jamieson Christian

          Maddy,

           

          Given that BUDGET is defined at the month level and ACTUAL is defined at the day level — two different levels-of-detail (LODs) — I recommend that you use Data Blending rather than row-level table joins. Data Blending allows you to aggregate before joining, which is helpful for resolving data sources that are at different LODs.

           

          Attached is a workbook (version 10.1.4) where I attempted to a Data Blending approach. Here's what I did:

           

          1. Bring in BW and Budget as 2 separate data sources.

          2. Pivot Budget so that all the months are their own rows.

          3. Create a calculated field [Join month] in both data sources. For BW, this is just DATETRUNC('month',[Calendar Day]). For Budget, this is DATEPARSE('MMM-yy',[Month Year]) — where [Month Year] is what I called the Pivot field that contains the budget months.

          4. Set up [Concept] and [Join month] as Data Relationships.

          5. In Budget, create a calculated field [Value to report] that pulls in SUM([Actual USD]) from BW or SUM([Budget]) from Budget, depending on whether the given month is in the past. (I assumed, also, that Budget is in 000's and multiplied appropriately, so that BUDGET and ACTUAL are in a similar range.)

          6. Set up the view with Budget as the primary data source and [Value to report] as the numbers to show.

          7. Filter on [Value to report] is not null, to get rid of years that (1) are in the past and (2) have no ACTUALs to report. This effectively filters out everything prior to 2017 (since there are no ACTUALs for past years), as well as any Concepts that have neither BUDGET nor ACTUAL in 2017.

           

          End result:

           

          Let me know if that's what you were looking to do.

          • 2. Re: Trouble Combining Actual & Budget Data (Different sheets)
            Maddy Pena

            Jamieson, thank you very much!


            It is exactly what I needed!