Hello QC!
Need some help with a Pivot Table today. My logic/SQL is a bit rusty (haven't done development in a while) so I'd appreciate the help tremendously.
I am creating a table that has 11 different metrics/calculations grouped by years (2013-2016, see column 1), and also measured across years (need years on horizontal and vertical rows). The calculations is to provide metrics about Donor Data for our non-profit for the last 4 years.
There are two metrics that are needed for most of the others that I need help calculating.
1) Original Donors - [OD] - The number of Donors whose first gift was given in 2013 (or selected [First Gift Year]).
2) Original Sustainers [OS] - The number of Donors that gave in a year (2013-2016) that became first time Donors in 2013 ([OD]). So in this case the value for [OS] in 2013 will equal the value for [OD] in 2013; Same for other years.
Any thoughts on how to write these Count/If statements?
It hopefully will look something like this:
| First Gift Year | Metric | 2013 | 2014 | 2015 | 2016 |
|---|
| 2013 | Original Donors | 25,000 | - | - | - |
| Original Sustainers | 25,000 | 10,000 | 7,000 | 4,000 |
| YOY Retention | - | 40% | 70% | 57% |
| Base Retention | - | 40% | 28% | 16% |
| 2014 | Original Donors | - | 40,000 | - | - |
| Original Sustainers | - | 40,000 | 15,000 | 2,000 |
| YOY Retention | - | - | 37.5% | 13.3% |
| Base Retention | - | - | 37.5% | 5% |
| 2015 | Original Donors | - | - | 65,000 | - |
| Original Sustainers | - | - | 65,000 | 5,000 |
As usual, if this isn't clear, or you need more info, let me know. My SQL needs polishing, so any help is appreciated!
Thanks in advance to you Qlik Wizards!
-misterk
