Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In a pivot table I have created as a column: current 12 months according to: if (Period> Year (AddMonths (Today (), - 13)) & Num (Month (AddMonths (Today (), - 13)), '00' ),
Then as a measure I want to get the number of customers (Nrb_Cust_Leavs) who unsubscribe as a percentage in relation to the number of customers (Nrb_Cust).
This should be rolling so 202103 figures should sum up "Number of customers who have left" / "number of customers" calculated for the period 202004 - 202103
Uses this function to try to calculate the percentage of rolling 12 months:
if (Period> Year (AddMonths (Today (), - 13)) & Num (Month (AddMonths (Today (), - 13)), '00'),
sum (Nrb_Cust_Leavs) / Sum (Nrb_Cust)
)
As the picture shows, it calculates that 0.66% of the customers who have left have summed up in 12 months, but it should be 0.81% calculated on:(125115/1052).
What am I doing wrong?
I think i got a solution:
Create a new table:
PeriodNums:
Load distinct Period, AutoNumber(Period, 'PeriodNumCounter')*-1 +1 as PeriodNum Resident Facts order by Period desc;
then create a measuer:
Sum({$<PeriodNum={">-12"}>}[Nrb_Cust_Leavs/Nrb_Cust])
I think i got a solution:
Create a new table:
PeriodNums:
Load distinct Period, AutoNumber(Period, 'PeriodNumCounter')*-1 +1 as PeriodNum Resident Facts order by Period desc;
then create a measuer:
Sum({$<PeriodNum={">-12"}>}[Nrb_Cust_Leavs/Nrb_Cust])