How to create calculated field for future dates?

Q&A
, , , ,
1

Hello,

I have a dataset that contains a cost field and a date field. This dataset has 12 data points, as the date for these costs is monthly.

I want to create a bar chart that will display the costs as Y-axis and date as X-axis. This is straightforward. However, I also want to show the next 12 months as a prediction or forecast for the next year. The calculation for future months will simply be (current month cost - previous month cost) / (previous month cost) * current month cost

What is the best way to create a calculated field that shows after my existing dataset’s costs, those new costs, and also display them for the next 12 months?

edit: i had written (current month cost - previous month cost) / (previous month cost) * current month as my calculation, but that was wrong. it is supposed to just all be costs, so (current month cost - previous month cost) / (previous month cost) * current month cost

2

You cannot multiply a cost field ($ or numeric) with a date field (current month). Use the Forecast feature of Quicksight instead.
Steps -

  1. Click on the three dots on the top right of the visual
  2. Select ‘Add forecast’
    *Note - Forecast only works if you aggregate date field by Month.
    Attaching screenshots for your reference. Hope this helps.
    Thanks!
    QS_Forecast1
    QS_Forecast2
    QS_Forecast3
    QS_Forecast31367×465 45.9 KB
1 Like
3

Thanks for responding! I have a couple questions -

  • My dataset is only 12 datapoints for months, I think this article says that I need at least 43 data points to use forecasting. Is that true?
  • When trying to add forecasting to my bar chart, I do not see it after clicking the 3 dots on the top right of the visual. Is this because I do not have enough datapoints, or is there another reason that I would not see it?
4

That’s correct.

  1. Ideally, having more data points from the past months will give a better forecasting ability but this is not a blocker.
  2. Forecasting only works with certain charts like line chart, combo chart and others but not bar charts.
  3. For solving recursion problem for cumulative sum of cost, try editing your dataset and adding a new calculated field here (instead of creating a new calculated field in your quicksight visualization).
    Formula: runningsum(sum({Cost}),{Month},‘YTD’)
    Dataset sample:
    | Month | Cost |
    | Sep-23 | 100 |
    | Oct-23 | 200 |
    You would first have to recreate all your other formulas in the dataset and also change the date field to ‘Month’ as shown above. Hope this helps!
    Thanks!
2 Likes
5

Using Amazon Forecast service could be another potential solution instead of creating calculated fields in Quick Sight. Follow the below steps to setup the same -

  1. Configure Forecast with a range of prediction Quantiles (0.1, 0.9 etc) on your s3 dataset.
  2. Dump the forecasted dataset back to your s3 bucket.
  3. Visualize it with Quicksight as a regular dataset.
    It also gives you the ability to control the prediction parameters than manually using mom to predict future costs. Hope this is helpful.
    Thanks!
2 Likes