periodOverPeriodDifference does not take into account leap years if done in Year level

0

In the following blog post: https://aws.amazon.com/blogs/big-data/add-comparative-and-cumulative-date-time-calculations-in-amazon-quicksight/ under Advanced use case 3: Partial period comparisons for partialQoQQTDSales comparisons if this formula is used in conjunction with RunningSum to measure partial year this year to date vs last year to date and if one of the year is a leap year, the calculations will take into account one more or less day (depending if the leap year is a prior year or this year).

Using the blogpost example, to replicate this issue, one can try this:

PartialYoYYTDSales=periodOverPeriodPercentDifference(runningSum(sumIf(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("YYYY",now()), now(), "HH"), "HH", truncDate("YYYY", {Order Date}))),[truncDate("HH",{Order Date}) ASC],[truncDate("YYYY",{Order Date})]),{Order Date}, YEAR, 1)

if now() is a date in a leap year ,the prior year invoked will have 1 more day being added into the running sum calculation for last year. i.e. if now() is Mar 6 2020, prior year running sum calculation will include Mar 7 2019 as an extra day (from Feb 29 2020) is added into the periodOverPeriodPercentDifference calculation.

This also affects all periodOverPeriod series of functions.

Please advise if this could be fixed or looked into. Thank you.

jw321
asked a year ago46 views
No Answers

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