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
preguntada hace un año47 visualizaciones
No hay respuestas

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas