periodOverPeriodPercentDifference / periodOverPeriodDifference cannot work with year

Question

Using the same example from: https://aws.amazon.com/blogs/big-data/add-comparative-and-cumulative-date-time-calculations-in-amazon-quicksight/

I am able to replicate both Quarter and Month Partial QoQ QTD Sales and MoM MT Sales:

PartialQoQQTDSales=periodOverPeriodPercentDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("Q", ${DateParameter}), ${DateParameter}, "DD"), "DD", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

PartialMoMMTDSales=periodOverPeriodPercentDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("MM", ${DateParameter}), ${DateParameter}, "DD"), "DD", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

However, Year to Date Partial YoY YTD Sales would not produce the desired result with:
PartialYoYYTDSales=periodOverPeriodPercentDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("YYYY", ${DateParameter}), ${DateParameter}, "DD"), "DD", truncDate("YYYY", {Order Date}))), {Order Date}, YEAR, 1)

any ideas what did I do wrong?

Answer

Hi there,

Thank you for posting the question and the detailed explanation. In context regarding [periodOverPeriodPercentDifference](https://docs.aws.amazon.com/quicksight/latest/user/periodOverPeriodPercentDifference-function.html), YEAR is also one of the valid granularity.

In order to assist you better a screen-share session would be needed. Can you please open a support case with AWS using the following [link](https://support.console.aws.amazon.com/support/home#/case/create)

periodOverPeriodPercentDifference / periodOverPeriodDifference cannot work with year

Contenuto pertinente