Calculation Aggregation Errors

I have a sample dataset that looks like this:

image

I want to calculate the cancelation rate for every month of user signups. For example, in the month of December, there have been 5 signups and 2 cancellations. I want to create a calculated field that spits out 40%.

When I try this (sample Arena dashboard included), I get this error: Table calculation attribute reference(s) are missing in field wells. This is my formula for the Cancelation Rate field:

countOver({cancelation_date}, [{signup_date_cohort} ASC])
/
countOver({signup_date}, [{signup_date_cohort} ASC])

I did a countOver as I need this for another visual where it needs to count all cancellations over all signups over the entire date cohort (e.g. one month; the calculation for this field is truncDate(“MM”, {signup_date})). I also want to do a runningSum of the cancellations divided by the total number of signups, but this is not working as well.

How could I solve these two problems? I appreciate your help!

https://community.amazonquicksight.com/#Arena-dashboardId=9bbfee51-bcab-4545-a58c-7f54e80fd307&authorId=CU-10401

Hi @milofels
could you please check your link?
BR

Link works for me.

When I copy the link from here:

This is what I get:

Test 03/20

Now it’s working.

With the current formula you need to bring signup and cancelation date into the table because of the partition.

Hi Erik, thanks for your help.

I want to show this in a table ultimately and dont have space to select those two fields as well. Any other way I can change the formulas so I still get the desired output without needing to intervene at the data level?

Hi @milofels
please check Test 03/20_EG
BR

1 Like

Hi @milofels
Any updates on your side?
BR

Hi @ErikG,

thanks for your analysis and your continious help with this, I appreciate it!!! The problem with the Cancellation Rate 2 field is that it picks up the overall cancellation rate for each month, but not the cumulative cancellation rate for every cancelation day grouped by signup month.

In the end, we need a line chart visual that can depict the following:

on the x-axis we have “cancellation days”, which are the days between signup_date and cancelation_date.

on the y-axis we have the cancellation rate.

and we have multiple lines for each signup_date_cohort. For example, there would be one line for all November signups (let’s say there were 80 signups in November). On day 1 maybe 20 of them canceled, on day 2 maybe 10 canceled, on day 3 maybe 30, on day 4 maybe 0. The respective cancelation rate on the line graph should look like this:

day1: 20/80 = 25%
day2: (20+10)/80 = 37,5%
day3: (20+10+30)/80 = 75%
day4: (20+10+30+0)/80 = 75%

This somehow doesnt work for me without running into aggregation errors in my calculated fields…

Hi @milofels,
It’s been awhile since we last heard from you. Are you still having problems with your original question or were you able to find a work around. If you are still looking for additional assistance, please update the Arena with any tests that you’ve run since the last communication.

Thank you!

Hi @milofels,
Since we have not heard back from you, I will archive this topic. If you need further assistance, please post a new question in the community and link to this topic to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts.

Thank you!