If possible, could I get some assistance with an issue I’m having.
It’s all based on recurring sales.
There’s a Sales dataset with all the transactions.
The Pledge Stats dataset details of the recurring pledge including start date and cancel date.
My manager wants a chart for each sales group showing how many cancelled that week as a percentage of the number of people who were active at the beginning of the week in that group (i.e. had made four sales at the beginning of the week.)
For example, for the chart with the legend of ‘Sales Group 4’, this is the group that at the beginning of the week were active (Circle Open < the beginning of the week date and Circle Current = ‘Yes’) and had four sales transactions. By the end of the week, some would have cancelled (based on circle closed date). So the data point on the chart would be (those who cancelled during the week who had four sales at the beginning of the week) / (all those who were active at the beginning of the week with four sales).
Some customers might have made their fifth sale during the week but the active number is based on the beginning of the week.