Hi Sanjay

    See the attached T10.1 file

I think this is what you want - couple things to note - you can change the start of the fiscal year by Right clicking the Date (in the Dimension frame) >> Default Properties >> Fiscal Year Start then select the month

Then to get the calculation started there was no initial members - or 0 the start year 1, also the net numbers added in the first FY was also 0 - I assume that is just a fluke in sample data -

The formulas worked out to be

• CE>>RUNNING_SUM([Net added during year])
• CN>>Countd([Joined Date])
• CS>>Lookup(RUNNING_SUM([Net added during year]),-1)
• Closed by Year >> Countd([Account Closed Date])
• Net Added >> ([CN]) - ([Closed by year])
• Retention Rate (%) >> (([CE]) -([CN]))/([CS])

Hope this works

Let me know if it helped

Jim

Hi Sanjay,

Change the default properties for Retention rate to percentage.

Gourav

Hi Sanjay,

I was thinking of the same yesterday only that the closed accounts should be considered in the finacial year they fall.

Below is the .twbx file where I tried to get details as per expected.

Hope this helps.

Thanks,

Gourav

Hi

Sorry I had expected that the FY designation would work in calculations - it did not - I see that Gourav provided an answer - does it meet your needs

Jim

HI

It disturbed me that I could not get this to work out so I spent some time with it this morning -

See the attached T10.1 workbook - The issue I kept running into was the 2 dates Joined date and Closed date attached to s single member ID - every time I tried to relate the two I simply looked at the common records - not all records

So the solution path was

• First I needed to have a filed for the FY Year - in each the Joined Date and The Closed Date
  • Those took the form New year>> IF Month([Account Closed Date])<=6 then Year([Account Closed Date])  else year([Account Closed Date]) +1 end
• Next I duplicated the date set and renamed the 2 Join Date and Closed
  • The I created a blended relationship between the to sets that linked the New Joined Year to the New Closed Year
  • That gave a way to treat the closed member counts separate from the joined counts but still have a relationship on the dates
• From the JOINED data set I set up the following calculations
  • CN>>Countd([Joined Date])
  • Running CN>>RUNNING_SUM([CN])
  • CE>>[Running CN ]-zn([Close dates].[running closed by year])
  • CS>>lookup([CE],-1)
  • Retention rate >> (([CE]) -([CN]))/([CS])
• From the CLOSED data set came the following formula
  • Closed by year >>{ FIXED [New year closed]:(if Countd(zn([Member ID Closed])) =0 then 0 else Countd([Member ID Closed]) End)}
  • Running total closed>> RUNNING_SUM(SUM([Closed by year]))
• Those yielded the worksheet below
• 
• I then hid a few fields to result in the following
• 
• and plotted out the graph
• 

Hope it makes sense - this was a much bigger problem than I first envisioned

Jim

Hi

I think you are almost there-

I looked at the data sources and the connection - it looks correct

The calculated fields are correct

Table calculations work by running along positions in the "table" in the view you are creating -

to do that you need to build the view in the a sequence consistent with the overall formula you are trying to model

In this case you need to apply the calculated fields to the table in the order shown in the attached -