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]))
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?
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