Period over period not showing values on dashboard

Question & Answer
,
1

I have a dashboard showing month over month % change and week over week % change in the same column, depending on the parameter value ‘month’ or ‘week’ selected.

The date dimension is in MMM dd, YYYY format, and for MoM % dashboard is not able to render for certain months (Oct, July, May 2024). But if I change the date dimension format to show month without date, then it will show up.
Also downloading csv from the Viz has the calculated MoM% as well.

image
image1246×1148 93.3 KB

Change the date aggregation to month would work, but because I also need to show WoW% I have to show the exact date.

image
image1246×1122 105 KB

2

Hi @jiecshen

Welcome to the QuickSight community!

Can you share the details of your calculated field to look at?

3

This is the calculated field for MoM/WoW. I have a parameter has ‘week’ and ‘month’.

ifelse(

${viewBy} = 'week', periodOverPeriodPercentDifference(sum({metric_value}),{period_end_date},WEEK,1),

periodOverPeriodPercentDifference(sum({metric_value}),{period_end_date},MONTH,1))
5

Hi @jiecshen,
It’s been awhile since last activity on this thread, are you still encountering the same issue or were you able to find a work around?

What if you created a second ifelse statement (based on your parameter) for your date field that changes the date format as well? That way it’s utilizing the format that works for either MoM or WoW. I don’t have a similar sample dataset to test this on but wondering if that could work?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi @jiecshen

It appears to me that the issue is with months that have 31 days.

I am not sure if your date column has a time component. I would suggest to a truncDate(‘DD’, {period_end_date}) instead of directly using the date column in your calculated field and then try.

From the example in the documentation things seem to work fine for all cases. So, if the issue persists after the above changes I suggest you raise a ticket with AWS to seek help.

Regards,
Giri

7

Hi Giri,

The date column doesn’t have time component, but truncDate does work!

I created this date field to use in the Viz and the period over period function.
ifelse(${viewBy} = 'week', {period_end_date}, truncDate("MM", {period_end_date}))

My only issue now is that it’s showing Oct 1, 2024, instead of month end date.
But I guess I will just have to educate the users for that.
Thank you again for the help!

8

Hi @jiecshen

For asthetics you could do the following to keep the users happy!
a. Trunc Date so you gets the beginning of the month
b. addDateTime (1, “MM” … adds one month to it so get to the beginning of next month
c. addDateTime(-1, “DD” …deducts one day so get end date of the month

ifelse(${viewBy} = ‘week’, {period_end_date}, addDateTime(-1, “DD”, addDateTime(1, “MM”, truncDate(“MM”, {period_end_date}))))