Please find attached a workbook that answers your first two questions. If you would be able to clarify what you mean by total investments, I might be able to assist you a bit better for question 3.
Basically what I have done in my attempt is creating a calculated field that identifies whether a date is within the specified time period. And with a filter, I filtered it so I could only see the status I would like to analyse - for example RED or AMBER.
I hope that is helpful to you.
Sri K Sample twbx.twbx 339.4 KB
Sorry for the delay in response.
Thanks for your response.
Requirement was to calculate the below information:
- RAG status of Red > 1 month
- Amber status > 3 months
- Not Reported > 45 days, and(from database itself we are getting more than 45 days information for N/R)
- % = (Red > 1 month + Amber > 3 months + N/R > 45 days ) / Total investments
Issue 1 :I can able to calculate the a, b, c in different sheets(PFA workbook). But as per the requirement we should display everything in single sheet(How I have achieved: each sheet I have changed the snapshot month filter based on the requirement)
Issue 2 : I am unable to get the % calculation due to changing the snapshot date filter based on requirement for each RAG.
added the updated workbook(Sample v2) at main thread.