I have a use case to show projections on a pivot table like so:
Currently, pivot table is created via independent calculated fields like so. The actual pivot table is also multiple visuals, with one pivot table for each year.
I would like to use a parameter or some other mechanism which will pass multiple values to QS. Then, create ifelse calculated fields like so. This would enable me to use one visual and reduce the number of calculated fields. Assume I cannot change the underlying dataset and have to do this at the quick sight level.
You cannot use multi valuable parameters with a quicksight calculated field.
You also cannot have aggregated rows be rows.
I would look to use SQL to edit the dataset so that you have rows of usage, prof services, as well as years / customer.
If your dataset looked like this.
client | Year | calculation | value
Customer A | 2022 | usage | 80
Customer A | 2022 | prof services | 10
Customer A | 2021 | usage | 20
Customer A | 2021 | prof services | 10
Customer B | 2022 | usage | 200
Customer B | 2022 | prof services | 50
etc…
Then you can go into quicksight and make a calculated field that looks at the years and multiplies by your multiple. This will be dependent on how you want to filter / change your years.
You can either make a parameter than can pick the values [‘Year 1’,‘Year 2’,‘Year 3’] and then filters the visual to those values.
In your calculated field you would then multiply it by what year it is in compared with the max / minimum date.
Here is a calculated field that checks if it’s the minimum date and then returns a multiple. You can use this and add truncDates / Date Differences that checks what year it is.
