1 Reply Latest reply on Jan 16, 2018 1:00 PM by Jim Dehner

    Aggregate/Disaggregate Calculation Problem

    Kate Leander

      I have a workbook with two data sources - one is an Excel sheet that has a list of ongoing sales campaigns and their total budgets by month. The other is a Google Sheet that updates daily with new sales numbers for each product in each campaign. I've created a viz to show how sales numbers by campaign compare to the budget by campaign over the course of the month. I've also created a calculated field to give a projected sales number for the whole month, using this calculation:

       

      IF DATETRUNC('month', MAX([Date]))=DATETRUNC('month', TODAY()) THEN

      SUM([Spend])*DAY(DATEADD('month',1, DATETRUNC('month', MAX([Date])))-1)/DAY(MAX([Date]))

      END

       

      All of this works fine - but I'm running into difficulty with the final part of my viz. I want to subtract the Projected Budget calculated field I created from the Total Budget field, which comes from my other data source, so that I can show whether we're on track to use the Total Budget by the end of the month. I created a simple calculated field to do this:

       

      SUM(Real Budget) - Projected Budget

       

      This seemed to work when I set it up a few days ago, but I looked today and the SUM(Real Budget) - Projected Budget figure is way off. I thought this might be an issue I could solve with an LOD expression (I'm new to these, so I could be wrong) - but I can't use an LOD when the figures used in the calculation each come from a different data source. I looked into Table Calculations as well, but was unable to find a solution.

       

      Apologies in advance - the viz is based on client data so I can't share it. I'm trying to create a workbook to mimic the problem, so I will try to post this later. In the meantime - any idea what might be going on and how I can solve this?

        • 1. Re: Aggregate/Disaggregate Calculation Problem
          Jim Dehner

          Hi Kate

          Yes pretty tough without seeing your data and workbook -

          you said it worked fine until a couple of days ago - have you seen it working successfully after the 1st of the year? and is Date in January ?

           

          Try taking your formula apart and looking at the real budget total and projected budget separately - you can use window_sum to sum the values

           

          Jim