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
gefragt vor einem Jahr47 Aufrufe
Keine Antworten

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen