periodOverPeriodLastValue not working

I have the below table and I want to create a line visual in quicksight where I will have the average lifecycle per month for each year. I am trying with periodOverPeriodLastValue but with no luck.

id back_for_review calendar lifecycle
23057 No 7/1/2023 0:00 13.89742452
23272 Yes 7/1/2023 0:00 1.702441659
23677 No 4/1/2024 0:00 266.7085423
23726 No 7/1/2023 0:00 3.0441441
23982 No 9/1/2023 0:00 47.34351031
23983 No 9/1/2023 0:00 47.36860925
24132 No 4/1/2024 0:00 255.7547144
25604 Yes 4/1/2024 0:00 226.6450698
26071 No 4/1/2024 0:00 217.5299647
26724 No 4/1/2024 0:00 190.1641559
26861 No 4/1/2024 0:00 203.4811341
27348 Yes 7/1/2024 0:00 280.8698881
27497 Yes 1/1/2024 0:00 91.01371778
27564 No 10/1/2023 0:00 0.148088033
27587 Yes 4/1/2024 0:00 177.6622981
27587 No 4/1/2024 0:00 177.6622981
27935 No 4/1/2024 0:00 174.7326077

So I will have something like this where one line (the blue) is the average lifecycle per month this year and the other is the average last year.

Hi @pantelis

Can you provide the details of your calculation using periodOverPeriodLastValue and explain the problem a bit more in detail?

Regards,
Giri

Hi @Giridhar.Prabhu , thanks for the reply.

My calculated field is this
periodOverPeriodLastValue(avg({da_lifecycle}), calendar,YEAR, 1)

and I get this line chart

if I filter only for this year I get this

while I want the below. One line to show the average lifecycle for last year in all months and one line to show this years up until now.

Hi @pantelis

  1. Create two calculated field to get Month (as in Jan, Feb, Mar etc) and another one to get Month in integer format from your date field. The calculated field with Month names cane be used on the visual and use the integer one for sorting as an off-visual field

  2. Create two calculated fields for Current Year and Prior Year data. Please refer the article below
    Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

  3. Use the Month Calculated field along with the two calculated fields for current and last year

  4. Do not filter the visual using the date field as the date filtering is taken care in the calculated field.

Thanks @Giridhar.Prabhu .

I have calculated for this year and the calculated months but this gives me the sum of all ids including double counting while I want the average. Let me explain in the with the below image

I have:

Lifecycle This Year = ifelse(dateDiff(calendar,now(),“YYYY”) = 0 AND calendar<=now(), {lifecycle}, 0)

IDs This Year = ifelse(dateDiff(calendar,now(),“YYYY”) = 0 AND calendar<=now(), {id}, 0)

So the two first columns give us the sum of lifecycle and the sum of IDs and the third gives the average. So, 39,805.03 / 2,838 = 14.03 but this is not correct as we have multiple rows of ids with the same lifecycle and I want it once.
I tried to find the average per id as:
Lifecycle per ID = avg({Lifecycle This Year},[{id}])
and the distinc count of IDs.
And then the average as
sum({Lifecycle per ID}) / distinct_count({IDs this Year})
so all in all the correct number should be :
20,572.21 / 888 = 23.17

Which I have in the last column of the table

But when I try to create a line visual with this field I get the below error.
where is the error and how to fix this?

Thanks

I managed to visualize it also in the lines chart by hiding the “other” categories

1 Like