YoY and MoM metrics up to certain day of year or month in QuickSight

1

MoM (Month over Month) KPI visual in QuickSight compares this month to last month, but using all the dates available for each month. How do you compare MTD over MTD? This is, this month up to date vs. the last month up to the same date, not the full month.

For example, you have all order sales with dates for October up to day13th (the most recent), and sales for September whole month. Sales for October from day 1 to 13 totals $30,000. But sales for whole September adds up to $90,000. When comparing MoM this shows that October is worst than September. Need to compare October sales from 1-13 vs September sales from 1-13, as this will show October (up to 13th) = $30,000 but September (up to 13th) = $25,000. So, in an equivalent time period October has more sales than last month.

Same case for comparing this year sales (up to the current day) vs last year (up to the equivalent date), so it is really comparable.

How can a KPI comparison make this calculation? Or what calculated field has to be created?

asked a year ago2399 views
1 Answer
1

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.

profile picture
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions