- Newest
- Most votes
- Most comments
There is no direct way of doing this in QuickSight, but you can use QuickSight functions in a calculated field to accomplish what you're trying to do. This example will target your Month-over-Month calculation, but could be used for Year-over-year as well. These calculated fields can be part of a dataset or part of an analysis. I suggest testing in an analysis before you alter your dataset.
First you will need to create a calculated field to track the running sum for the month, using the runningSum function. You will likely need to include other partitions if those are relevant to your dataset. https://docs.aws.amazon.com/quicksight/latest/user/runningSum-function.html
runningSum
(
sum({sale_amount}),
[truncDate("DD",{sale_date}) ASC],
[truncDate("MM",{sale_date}), truncDate("YYYY",{sale_date})]
)
Now that was the easy part. You should now have a field that is keeping track of your month-to-date value that will be used in the next field for comparison. The MoM calculation can be done using difference or percentDifference depending on what you're trying to show. This next calculated field is using the previous calculated field, which I named "Running Sum (Month)". https://docs.aws.amazon.com/quicksight/latest/user/difference-function.html
difference(
{Running Sum (Month)},
[{sale_date} ASC],
-31,
[]
)
Ok, that doesn't look so bad, but there's a problem, not all months have 31 days, so the index must be updated.
That should be easily accomplished using a dateDiff function, right? https://docs.aws.amazon.com/quicksight/latest/user/dateDiff-function.html
dateDiff({sale_date},addDateTime(-1,'MM',{sale_date})
However, if you give that a try QuickSight will return an error concerning the format of the "index" parameter for the difference function. The workaround makes the final result a little more complex. We workaround the problem by using another two functions, ifelse and avgIf, to give us a case statement depending on the MoM date comparison and the number of days in the previous month. https://docs.aws.amazon.com/quicksight/latest/user/ifelse-function.html & https://docs.aws.amazon.com/quicksight/latest/user/avgIf-function.html
ifelse(
avgIf(dateDiff({sale_date},addDateTime(-1,'MM',{sale_date})),dateDiff({sale_date},addDateTime(-1,'MM',{sale_date}))=-31)=-31,
difference({Running Sum (Month)},
[{sale_date} ASC],
-31,
[]),
avgIf(dateDiff({sale_date},addDateTime(-1,'MM',{sale_date})),dateDiff({sale_date},addDateTime(-1,'MM',{sale_date}))=-30)=-30,
difference({Running Sum (Month)},
[{sale_date} ASC],
-30,
[]),
avgIf(dateDiff({sale_date},addDateTime(-1,'MM',{sale_date})),dateDiff({sale_date},addDateTime(-1,'MM',{sale_date}))=-29)=-29,
difference({Running Sum (Month)},
[{sale_date} ASC],
-29,
[]),
avgIf(dateDiff({sale_date},addDateTime(-1,'MM',{sale_date})),dateDiff({sale_date},addDateTime(-1,'MM',{sale_date}))=-28)=-28,
difference({Running Sum (Month)},
[{sale_date} ASC],
-28,
[]),
NULL
)
It's not pretty, but it might work for you.
Lastly I wanted to make sure you were aware of QuickSight Insights Period over Period Computation and Period to Date Computation.
https://docs.aws.amazon.com/quicksight/latest/user/period-over-period-function.html
https://docs.aws.amazon.com/quicksight/latest/user/period-to-date-function.html
These might be worth investigating as well, keeping the above guidance in mind.
Relevant content
- asked 6 months ago
- asked 8 months ago
- asked 4 months ago
- AWS OFFICIALUpdated a year ago
- Can I use ACM to issue private certificates when the AWS Private CA validity is less than 13 months?AWS OFFICIALUpdated 5 months ago
- AWS OFFICIALUpdated 4 months ago
- AWS OFFICIALUpdated 2 years ago