periodOverPeriodLastValue not working

[pantelis]

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.

[Giridhar.Prabhu]

Hi @pantelis

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

Regards,
Giri

[pantelis]

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.

[Giridhar.Prabhu]

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.

[pantelis]

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

[pantelis]

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

[Brett]

Hi @pantelis,
It looks like you were able to find a work around for your case, did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @pantelis,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!