Hello,
I need to create a retention chart (for the past 10 fiscal years) to compare this fiscal year, with last fiscal year, and calculate differences between the two.
- My dimension is Fiscal Year [FY].
- My expressions include the following [calculation is on the right]
1) [FY IDs] - Fiscal year distinct Id count: = count(distinct[Id])
2) [PFY IDs] - Previous fiscal year distinct Id count: = below(count(distinct[Id]))
3) [FY Total] - Fiscal year amount total: = sum([Amount])
4) [PFY Total] - Previous fiscal year amount total: = below(sum([Amount]))
5) [Overall Retention] - % ratio comparison in Id counts (#1/#2):
= (count(distinct[Id]) / below(count(distinct[Id])))
6) [Overall Revenue Retention] - % ratio comparison in total amount (#3/#4)
= (sum([Amount]) / below(sum([Amount])) )
All this works fine... but I need to calculate these...
7) [Retained IDs]
= count of distinct ids in this Fiscal Year, who were also in last Fiscal Year (intersection, not union)
😎 [Retention Rate]
= [Retained IDs] / [PFY IDs] or (#7/#2)
9) [Revenue Variance] (i.e. compare revenue for retained donors, only), expressed as a ratio
= [FY Total] / [PFY Total] * for [Retained IDs] only
10) [Retained IDs] per user selections. That is, include same user-selections for both Fiscal Years (for #7, #8, and #9).
For example, suppose a user selects several dimensions (e.g., division, account, and gift-type). Then, the comparison to the previous year summations should be for the same dimension (division, account, and gift-type). Again, a retained Id would only be distinct IDs that gave in both years (intersection, not union)… so, same Id list, same dimensions. Consider the following, however:
- a. If an Id gave in year 1, and then in year 2 [and no other dimensions were selected], then the Id is considered 'retained.’ So, the same Id gave in both Fiscal Years.
- b. If, however, an Id gave to one division in year 1, but to a different division for year 2, then that Id is not considered 'retained' from the viewpoint of that division….[’retained’ from a stand-point of the whole organization for the Fiscal Year, yes, but not from the stand-point of the specific division].
In other words, the status of ‘retained’ needs to match the user selections… same dimensions and same Id list. That is, count the Ids who in both Fiscal Years, for the specific set of dimension selections.
I have a sample qvw, called “retention example.qvw.” When it compares years, it only counts unique IDs, for a given year. Moreover, it can filter Ids for a dimension, by association. But these are only Fiscal Year + Dimension counts – not a count of distinct Ids that gave both years. The actual Id lists are different. The below() and above() functions don’t consider Ids that in gave both years (i.e., actual Is that were retained)…
Questions:
1) What is the best approach to this?
- Set Analysis?
- Alternate State? Is it possible to do an alternate state? [Perhaps get current selections from Group A and set them in Group B (but have Group B as the previous year) …if setting dynamic selections is possible?]
- Pre-calculate this all via SQL or script… prior to loading the data?
- Using an inline-table [i.e. list of Fiscal Years, Previous Fiscal Years…and perhaps associating these years with the gift data?]
- Functions (though I’ve tried many: previous(), peek(), fieldvalue(), etc.)
- Self-Join?
2) For #7, how do I select Ids from this Fiscal Year, who were also in last Fiscal Year? Something like this [though none of these work]:
count(DISTINCT {1<[FiscalYear] = {$(=FiscalYear)} >} [Gifts Id])
count(DISTINCT {1<[FiscalYear] = {$(=FiscalYear)-1} >} [Gifts Id])
count(DISTINCT {1<[FiscalYear] = {$(=year(only(FiscalYear)-1))} >} [Gifts Id])
3) Then I need to determine the intersection
count(DISTINCT {$<FiscalYear=($(='FiscalYear'))>}*{$<FiscalYear=($(='FiscalYear')-1)>} [Gifts Id])
Note…I’ve had difficulty showing set analysis expression results…for every row of a straight table. The expressions I’ve tried don’t evaluate in a row … unless a Fiscal Year is selected by a user, and then the calculation works only for one row (and only displays for one row).
- Even if I use a '1' instead of '$' the table doesn't populate for each row:
count(DISTINCT {1<FiscalYear={ $(=year(only(FiscalYear)-1)) } >} [Gifts Id])
I need to have the counts for all rows
4) Do you have suggestion on how to I handle #9, #10?
Thanks in advance, for any suggestions,
Jarrell
