Get last year value (periodOverPeriodLastValue) in a PRE_AGG scope

Question & Answer
,
1

Hi All,

i have the following situation:

image
image1118×318 41.7 KB

I need to get the last year values, I do with periodOverPeriodLastValue function. The issue is coming when I need to add a field that is limiting the scope of dates used in this function, to just the dates of the current year…

I thought to use a sumOver with PRE_AGG and try to calculate the periodOverPeriodLastValue before doing the aggregation with this field, but QuickSight is giviming me error.

Formula:
periodOverPeriodLastValue(sumOver({value},[{date limiting to current year}],PRE_AGG), {dates}, DAY, 364)

Error:
image

image
image736×244 3.69 KB

2

I’m a little confused. Why are you limiting your sum to only be the current year? Don’t you want both if you are comparing?

3

Because I have a calculated field, that contains the week, month or quarter date… based on what I choose from parameter… and since I cannot format the dates in the calculated field itself, i need to proper format them as “2023-W12” for week, “2023-01” for month and “2023-Q1” for quarter… i do this with other fields and since there is the year, these are limiting the scope

4

I’m also having this problem. Trying to compare sales of this year with the previous year month by month (but without showing in the table the values of last year in the columns).

periodOverPeriodDifference(
sumOver(Real, [Date], PRE_FILTER),
Date,
‘YEAR’,
1
)

And receiving the same error message.
I could use this:

periodOverPeriodDifference(
sum(Real),
Date,
‘YEAR’,
1
)

But this only works if I show the sales of last year (which I don’t want to).

1 Like
5

This is a limitation of quicksight for now. You need to show the other years if you are comparing.

I can mark these as feature requests.