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.
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