periodOverPeriodLastValue skipping months

Question & Answer
,
1

The AI recommended “periodOverPeriodLastValue” as a great way to get what I want in a calculated field. But it only works sometimes and skips some months? What’s up with that?

I need to use the prior month’s value in some calculations. Even creating a dummy dataset here, you can see that sometimes the correct prior month value is populated, but other months are not. It’s sorta an alternating pattern.

My dates are the last day of the month, perhaps that creates a problem somehow?

periodOverPeriod Error

2

Hi @Shannon ,

welcome to the community :tada:

The Lag function gives you the value from the previous line.

lag(sum({Value}), [{month_end_day} ASC], 1)

Best Regards,
Nico

PS: I tried to truncDate your date field, but when I do the periodOverPeriodLastValue function shows nothing.

1 Like
3

Thanks Nico,
The lag function is helping me some, but my real data has many slices, so I’m having trouble getting the lag function to work over all the groups required.

Lag feels a bit brute; “Give me the row above” vs “give me this value only a month ago” which is what I’m looking for.

4

Hi @Shannon ,

I found a solution (by coincidence).

Only change the aggregation of your date-field to month.

Before:

image
image940×503 47.3 KB

And After:
image

Best regards,
Nico

1 Like
5

Thanks, Nico! That is working great now.

I assume it has to do with the end of the month date values doing something odd, so it makes some sense that aggregating them by month would eliminate that problem. Still seems like an underlying bug, but happy to have a work around.

1 Like