Forum Discussion
Accumulated values for Cohort Visualization
Hi Everyone, I'm Trying to make a Cohort Viz, but something has been bugging me for a while.
The table i'm using has client data, so i am not able to share it. But i'm dealing with users that has to pay monthly fees, and one of its visualizations is the Churn Cohort, which means is the people that drops out of the contract.
Its rules goes as follows:
Whenever someone drops out of the contract, check the month the user dropped, and check which parcel the user dropped. The user will then be accounted for in these 2 criteria, as shown above.
But, i want to transform the measure into a Accumulative Measure, but I am not pulling this through.
the visual i'm looking for is something like this:
.....
Date = M1 | M1+M2 | M1 + M2 + M3 | . . . | Mn
What am I doing it wrong in here?
Here's the measure:
Cohort Churn =
VAR primario =
CALCULATE(
[qtd_mensalidades],
FILTER(
fMensalidades,
[mes_venda] <= TODAY() &&
[numero_mensalidade] = DATEDIFF([mes_venda], [mes_devolucao], MONTH) + 1 &&
[status] = "Devolvido"
)
)
RETURN
primario
for some info about the table:
[month_sold] is the month the user signed the contract
[month_dropped] is the month the user dropped out
[parcel_number] is the number of the parcel of that entry.
[status] is the status of that user. can be dropped, active, late, etc.
The table has data of all the parcels the user has to pay, so in example:
the user 90182 signed a contract, now in the database it wil be created automatically all the parcels and thates the user has to pay, something like this:
| simulation_id | parcel_number | month_sold | month_returned | status | expected_month_payment | month_payment |
| 90182 | 1 | 1/10/2024 | 1/12/2024 | Dropped | 1/10/2024 | 1/10/2024 |
| 90182 | 2 | 1/10/2024 | 1/12/2024 | Dropped | 1/11/2024 | 1/11/2024 |
| 90182 | 3 | 1/10/2024 | 1/12/2024 | Dropped | 1/12/2024 | 1/12/2024 |
| 90182 | . | . | . | . | . | . |
| 90182 | 12 | 1/10/2024 | 1/12/2024 | Dropped | 1/10/2025 |
in this scenario, the user would be in accounted for in Dez 2024 in Month 3.
- Anonymous1 year ago
Problem was, in the measure was accounting for only the parcel number associated with the month, not joining with other parcels. So, I needed operator logic to accont for that. Thought that fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda],fMensalidades[mes_devolucao], MONTH) + 1 would work, but it was aggregating values only onto the 1st parcel. So i did the opposite:
Cohort Churn =VAR Primario =CALCULATE([qtd_mensalidades],FILTER(fMensalidades,fMensalidades[mes_venda] <= TODAY() &&fMensalidades[numero_mensalidade] >= DATEDIFF(fMensalidades[mes_venda], fMensalidades[mes_devolucao], MONTH) + 1 &&fMensalidades[status] = "Devolvido"))-- Check if there is any parcels in the current modelVAR ValidacaoParcela =CALCULATE(COUNTROWS(fMensalidades),FILTER(fMensalidades,fMensalidades[mes_venda] <= TODAY() &&fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda], TODAY(), MONTH)))-- Return if availableRETURNIF(ValidacaoParcela > 0, Primario, BLANK())
3 Replies
- AnonymousNot applicable
Problem was, in the measure was accounting for only the parcel number associated with the month, not joining with other parcels. So, I needed operator logic to accont for that. Thought that fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda],fMensalidades[mes_devolucao], MONTH) + 1 would work, but it was aggregating values only onto the 1st parcel. So i did the opposite:
Cohort Churn =VAR Primario =CALCULATE([qtd_mensalidades],FILTER(fMensalidades,fMensalidades[mes_venda] <= TODAY() &&fMensalidades[numero_mensalidade] >= DATEDIFF(fMensalidades[mes_venda], fMensalidades[mes_devolucao], MONTH) + 1 &&fMensalidades[status] = "Devolvido"))-- Check if there is any parcels in the current modelVAR ValidacaoParcela =CALCULATE(COUNTROWS(fMensalidades),FILTER(fMensalidades,fMensalidades[mes_venda] <= TODAY() &&fMensalidades[numero_mensalidade] <= DATEDIFF(fMensalidades[mes_venda], TODAY(), MONTH)))-- Return if availableRETURNIF(ValidacaoParcela > 0, Primario, BLANK()) - AnonymousNot applicable
Hi, Anonymous
Perhaps you can try to modify the Measure to the following formula:
Cohort Churn Accumulated = VAR CurrentMonth = MAX(fMensalidades[mes_devolucao]) RETURN CALCULATE( SUMX( FILTER( fMensalidades, [mes_devolucao] <= CurrentMonth && [status] = "Devolvido" ), [qtd_mensalidades] ) )Please check if this Measure meets your calculation needs.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- AnonymousNot applicable
not quite.
all the values in the table ends up with all lines filled, and it does not relfect the accumulated sum.
I tried the following, and it worked only in certain instances...