I am trying to calculate the cancellation rate, but on a weekly level.
Previously in Tableau I created Weeks After Enroll, so basically weeks it took for a user to cancel. That I have calculated based on enrollment and cancellation date.
Then I created Enr LOD, { EXCLUDE [Weeks After Enroll] : COUNT([username])}
Another calculated field I have is cancellation, that is count(username).
The end product I want is running total of count(cancellation)/sum(Enr LOD)
Hello @patilmeet7, I think we can come up with a solution to calculate a weekly cancellation rate, as for a running total, it might become a little messy.
I would recommend utilizing countOver and sumOver functions in QuickSight, which will allow you to create an aggregate value based on a specific partition, in this case, weeks. That would allow you to get your count of cancellations each week and divide it by the sum of enrolled users.
I think it might be likely though that if you want to maintain a running sum of this aggregation, it is probably going to need to be done in your SQL query. Let me know if this helps at all, I think we can at least resolve your initial request this way.
Can you try below calculation for Enrollments : Countover({usernames})
I tried the below but did not get the expected result.
sumOver(count(username), [Weeks After Enroll])
For each Week After Enroll, I should get the same number. The expected output:
| Week After Enroll | Enr LOD |
|---|---|
| 1 | 12345 |
| 2 | 12345 |
| 3 | 12345 |
| 4 | 12345 |
Hello @patilmeet7, try utilizing a countOver rather than a sumOver with a count inside of it. It would look something like this:
countOver({username}, [{Weeks After Enroll}], PRE_AGG)
Let me know if that gets you closer to your desired result!
Hello @patilmeet7, since we have not heard back from you, I will go ahead and mark my previous response as the solution. If you have any further questions on this issue or you are still unable to get the desired output, please let me know. Thank you!