Hello! I am trying to use Looker to track daily spend for my company’s partners. I created a blend that pulls in spend data from three different data sources to account for each partner, then used a custom field “Daily Spend” with the following formula: SUM(COALESCE(Spend,Media Cost,Cost))
So far, I have a pivot table that shows daily spend for each partner in the current month. However, I use this tracker to pace our estimated spend for the entire month. Is there a way to replace the null values for future days in the month with a daily estimate for each partner? I’m open to different methods of achieving this, whether it replaces the null values with the partner’s average daily spend for the month so far or if there’s a way to input a chosen value in place of the nulls.
Here’s an idea of what the pivot table looks like so far for the current month (the rest of the dates in august would be null):
| Date | Partner 1 | Partner 2 | Partner 3 | Grand Total |
|---|---|---|---|---|
| 8/1/2024 | $300 | $100 | $0 | $400 |
| 8/2/2024 | $200 | $100 | $500 | $800 |
| 8/3/2024 | $300 | $100 | $200 | $600 |
| 8/4/2024 | $300 | $100 | $300 | $700 |
| 8/5/2024 | $300 | $400 | $100 | $800 |
| 8/6/2024 | $100 | $200 | $200 | $500 |
| 8/7/2024 | $200 | $100 | $500 | $800 |
| 8/8/2024 | $300 | $200 | $100 | $600 |
| 8/9/2024 | – | – | – | – |