Forecast value on demand

Question & Answer
,
4

Hi @Mayur1, to address the issue with the granularity level affecting your calculations for “cost_covered_till_date” and “forecast_value_remaining_days,” you can use calculated fields while ensuring that filters applied for one calculation do not inadvertently affect the other.

Here’s a step-by-step guide to achieve the desired result:

1. Calculate “Cost Covered Till Date”

You need to calculate the total cost covered from the beginning of the month until the selected date. Ensure that this calculation is independent of the filter used for the forecast.

sumOver(
    sumIf({Total Cost with CSP}, Application <= ${Dateselection2} AND Application >= truncDate('MM', ${Dateselection2})), 
    [Application]
)

2. Calculate “Forecast Value Remaining Days”

This calculation involves determining the run rate for the last 7 days and then forecasting the cost for the remaining days in the month.

(
    ({Total Cost Without CSP} / 7) * 
    (extract('DD', addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', ${Dateselection2})))) - extract('DD', ${Dateselection2}))
)

3. Combine the Calculations

To ensure that both calculations are added correctly, you need to make sure that the granularity level is fixed for both parts. You can combine them as follows:

sumOver(
    sumIf({Total Cost with CSP}, Application <= ${Dateselection2} AND Application >= truncDate('MM', ${Dateselection2})), 
    [Application]
) + (
    ({Total Cost Without CSP} / 7) * 
    (extract('DD', addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', ${Dateselection2})))) - extract('DD', ${Dateselection2}))
)

4. Ensuring Fixed Granularity

To ensure that the granularity level is fixed, use the sumOver function appropriately to aggregate over the desired level of granularity. This avoids the filters from impacting each other.

Final Calculation Example:

sumOver(
    sumIf({Total Cost with CSP}, Application <= ${Dateselection2} AND Application >= truncDate('MM', ${Dateselection2})), 
    [Application]
) + (
    ({Total Cost Without CSP} / 7) * 
    (extract('DD', addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', ${Dateselection2})))) - extract('DD', ${Dateselection2}))
)

In this calculation:

  • The sumOver function is used to calculate the “cost_covered_till_date” ensuring it aggregates over the correct granularity.
  • The forecast calculation is independent and uses a fixed date parameter to ensure accuracy.

Validation:

After implementing the above calculations, validate by comparing the calculated values with your expected results for a few sample months to ensure correctness.

Troubleshooting:

  • Ensure that the date dimension (Application) is correctly formatted and parsed in QuickSight.
  • Validate the intermediate results of each calculated field separately to debug any discrepancies.

By restructuring the calculations as above, you should be able to get accurate results for your forecast calculations without the filter affecting both the “cost_covered_till_date” and the “forecast_value_remaining_days”.

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and create a new post. (Details on using Arena can be found here - QuickSight Arena