My goal is to calculate retention, but my calculated field is showing 50% retention when the two underlying records that comprise that are both retained, so 100%. I am am calculating datediff between the start of a membership to today or the duration of a membership (I work for a member based company) in SQL. I then use a calculated field to determine if the days retained is above x number. I also have a filter to make sure every member I pull in has been active at least x amount - 90 days for example - I then created a calculated field dividing number of records by sum of members retained (the 0s and 1s from my calculated field utilizing).

Here, is the problem, for the region in question (so I could isolate the problem - where I only have two members, both retained past the point where they need to qualify my retention calculated field is showing 50%, but when I pull in their ID's each individual they individually show 100% retention and I pull that pill out and it aggregates again it shows 50%. I am hesitate to upload the workbook at this is proprietary, but if my description is unclear I can scrub it and upload it. Essentially my aggregation and deaggregated versions of my calculated field seem to behave differently and I am unsure why and I'm hoping this general issue someone has seen and solved before. I have changed the data types and the way it calculated (sum, avg etc) to no affect.

A workbook would help, but I'd first try a level of detail calculation. If you fix the datediff calc at the member level, then it should return the amount a the level of granularity you're looking for.

{FIXED [Member] : DATEDIFF('day',[Start Date],[End Date])}

You may have to do the same for the retention calc depending on how you're calculating it. Let me know if this doesn't work.

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.