Forecast value on demand

Question & Answer
,
1

Issue Description: When calculating forecast values manually in QuickSight, I encountered a problem with the granularity level affecting the addition of the “cost_covered_till_date” and the “forecast_value_remaining_days” for the month.

Problem Statement: The filter applied to calculate the “forecast_value_remaining_days” is inadvertently affecting the calculation of both the “cost_covered_till_date” and the “forecast_value_remaining_days”, resulting in inaccurate addition.

Description of Current Approach:

  1. Calculated “forecast_value_remaining_days” based on the run rate of daily cost for the remaining days of the month. This involved:
  • Utilizing a date parameter to filter the last 7 days to determine the run rate.
  • Multiplying the remaining days by the run rate to obtain forecast values.

calculation used is:
(({ Total Cost Without CSP})/7) * (extract(‘DD’,addDateTime(-1, ‘DD’, addDateTime(1, ‘MM’, truncDate(‘MM’, ${Dateselection2}))))-extract(‘DD’,${Dateselection2}))

It is giving me correct result.

Issue with Current Approach: While calculating the forecast on demand for the entire month (which is the sum of the “cost_covered_till_date” and “forecast_value_remaining_days”), the filter applied to “forecast_value_remaining_days” inadvertently affects the calculation by altering the granularity level.

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

this calculation is not giving correct answer.
Application is date dimension

Desired Solution: To ensure accuracy in the forecast calculation, I aim to fix the granularity level of both the “cost_covered_till_date” and “forecast_value_remaining_days” to today (from the first day of the month). Please note that I aim to calculate is using calculated field only, I am aware about the AI based solution to get forecast values but my requirement is different.

Request for Assistance: Seeking guidance on how to restructure the query or adjust the calculations to achieve the desired result without the filter affecting the “cost_covered_till_date” and “forecast_value_remaining_days”.

2

To address the issue you’re facing with the forecast calculations in QuickSight, the problem seems to stem between your filter application and how it influences the granularity of your dataset.

Here’s a refined approach that might help in aligning your calculations correctly for both cost_covered_till_date and forecast_value_remaining_days:

Revised Calculation Strategy

To ensure that the filters do not interfere with each other, you can encapsulate calculations in separate calculated fields or adjust the granularity directly in the formula itself. This can help in maintaining the intended level of granularity for each metric.

Step 1: Redefine the Calculation of cost_covered_till_date

For cost_covered_till_date, calculate the sum of costs up to the selected date but ensure that this calculation is independent of any filters applied to the forecast_value_remaining_days:

sumIf({Total Cost with CSP}, Application <= ${Dateselection2} AND Application >= truncDate(‘MM’, ${Dateselection2}))

This calculation should remain as is if it’s providing the correct values as per your current implementation.

Step 2: Adjust forecast_value_remaining_days

For forecast_value_remaining_days, you need to isolate the calculation so that it’s not affected by other filters. Use the same date parameter but ensure the calculation is done within a separate context:

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

This formula seems correct based on your description, as it calculates the remaining days’ forecast based on the last 7 days’ run rate.

Step 3: Combine the Results

To combine these calculations without letting the filters interfere with each other, you can use the sumOver function to correctly aggregate values over the specified dimensions without changing the granularity:

sumOver({cost_covered_till_date_calculation}, [Application], PRE_AGG) + {forecast_value_remaining_days_calculation}

Here, ensure that {cost_covered_till_date_calculation} and {forecast_value_remaining_days_calculation} are placeholders for the calculated fields or the actual formulas as defined above.

Considerations

  • Isolation of Calculations: Make sure that each calculation is sufficiently isolated in terms of applied filters and granularity. Use PRE_AGG to handle pre-aggregation in sumOver if needed.
  • Testing the Changes: After implementing the changes, test the calculations for a few different time periods and scenarios to ensure that the calculations are now accurate.
  • Debugging Further: If issues persist, consider adding intermediate calculated fields to break down the formula further or using print/log statements to check the values at each stage (if supported by your environment).

Implement these adjustments and check if the new formulas align better with your expectations for the forecast calculations.

Did this answer your question? If so, please help the community out by marking this answer as “Solution!”

1 Like
3

Tried this way, both calculations as an individual giving correct results but when adding together not giving correct answers filter is still affecting the cost covered till date,
when used calculations in a single calculated filed using formulae filter is affecting.

and with below given calculation it is giving error as “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.”

sumOver({cost_covered_till_date_calculation}, [Application], PRE_AGG) + {forecast_value_remaining_days_calculation}

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