Hello, I would like to know how to solve a problem in a pivot table, where I am calculating customer retention percentages, but when applying filters for plans, payment methods or country, the index 0 changes and is less than 100, when that is my base and should always be 100, I show the error in the table, and share the formula I am currently using, “distinct_count({User})/ sum(distinct_count({User},[MesRegistro]))” , I hope you can offer a solution.
hi @Juan,
thank you for posting your inquiry.
if applying filters is causing the issue, would you be able to try with distinctCountOver() with PRE_FILTER instead of distinct_count?
for example : distinctCountOver({User},,PRE_FILTER)/ sumOver(distinct_count({User},[MesRegistro],PRE_FILTER),,PRE_FILTER)
please let me know how it goes.
kind regards,
Wakana
1 Like
hello @Wakana , thanks for the reply, I have tried the recommended function, but it does not work because distinct_count({User},[MonthRegistration],PRE_FILTER) does not correspond to the supported syntax, the only way to not generate errors is as follows ‘distinctCountOver({User account}, ,PRE_FILTER)/ sumOver(distinctCountOver({User account},[MonthRecord],PRE_FILTER),,PRE_FILTER)’ but this does not solve my problem, since the whole table gives %100 retention, do you see any other solution?
Hi @Wakana i was not able to work this out, and i have a similar problem in other analysis, could you tell me if there is any documentation, similar solution, demo, so that i could solve this issue?
hi @Juan,
there is question in Community discussed about filter applying the specific fields. would you be able to take a look if this can be solution to your isssue?
kind regards,
Wakana