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
demandé il y a un an47 vues
Aucune réponse

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions