Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
janus2021
Contributor III
Contributor III

summary on rolling 12 months.

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?

1 Solution

Accepted Solutions
janus2021
Contributor III
Contributor III
Author

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])

View solution in original post

1 Reply
janus2021
Contributor III
Contributor III
Author

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])