Dynamic Offset within periodOverPeriodDifference

[ErikG]

Hi All,

can someone tell me if it is possible to get an dynamic offset within the periodOverPeriodDifference function?
Doenst matter if it is a calculated field or a ifelse direct in the function itself.

periodOverPeriodDifference - Amazon QuickSight

What we like to do is define the offset based on the difference of two year parameters.

Thx.

[duncan]

Hello @ErikG !

To clarify do you mean that you want to be able to choose the two different years and then the year difference between those creates your offset in the calculated field? For example, if I choose 2023 and 2020 my offset would be 3 years.

Could you try the following calcs:

Calc1 = dateDiff(${Year1}, ${Year2}, "YYYY")

Calc2 = periodOverPeriodDifference(sum(Sales),{Order Date}, YEAR, {Calc1})

I haven’t tested this yet so let me know if this works.

[ErikG]

Hi @duncan
thx for your reply.

Thats something i already tried. Calculation in a field in the formula itself. But looks like the only thing thats working is a parameter or fix value.

i guess i have to build a ifelse around with the calculation for every potential diff.

BR

[duncan]

Hey @ErikG!

Have you tried the solution given in this log? Looks like it might be what you are looking for:

Creating period comparisons with QuickSight's periodOver and periodTo functions

This article uses the SaaS-Sales Dataset that you can download as part of the QuickSight Author Workshop. We will update the data to the current date so your calculations are realistic. If you do not have a QuickSight account please follow the instructions in the Initial Setup. Instructions to create the dataset can be found in Exercise 1 of the Build your first dashboard. Create an analysis for the dataset. The following steps show you how to create visuals to show data from the current year,…

Specifically section 6!

[duncan]

Hey @ErikG !

Were you able to try the solution from the blog post above and if so did it work for you?

[ErikG]

Hi @duncan,
wasnt exactly what i needed. As the offset is based on a parameter and i dont want to use one.
BR

[Colin_Lindley]

I am getting an error when attempting this same thing, looks like there is a bug here.

Calculated Field:
periodOverPeriodDifference(sum({# Assets}), {Month}, MONTH, dateDiff(${DeepDiveDateParam1},${DeepDiveDateParam2},‘MM’))

Error: “At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again.”

When I create a calculated field with just the dateDiff part, I am getting an int. I have also tried to wrap the dateDiff in a min and minOver, with the same result.

[robert.eibers]

Hey @Colin_Lindley,

apparently you can only use hard values or parameter as an offset and not calculated values.
A workaround could be that you put an ifelse infront of periodOverPeriodDifference basiclly:
ifele(dateDiff(${DeepDiveDateParam1},${DeepDiveDateParam2},‘MM’) = x, periodOverPeriodDifference(sum({# Assets}), {Month}, MONTH, x)… and so on.
It would become a really large calculated field, depending on the number of possible offsets.
If you have any questions feel free to ask.

But dynamic offsets could be cool feature in the future.

BR
Robert