2 Replies Latest reply on Dec 4, 2018 10:11 PM by Sri K

    How to get more than 1month and 3months information

    Sri K

      Hi Friends,


      I have 3 columns,



      b. Date

      c. status.


      I want to calculate below things


      1. RED ID's-->more than one month(from today) how many ID's are red

      2. Amber ID's--> more than 3 months(from today) how many ID's are Amber

      3.% = (Red > 1 month + Amber > 3 months ) / Total investments



      Can you some one help me how to calculate.




        • 1. Re: How to get more than 1month and 3months information
          Joyce Luey

          Hi Sri,


          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.




          • 2. Re: How to get more than 1month and 3months information
            Sri K

            Hi Joyce,


            Sorry for the delay in response.


            Thanks for your response.


            Requirement was to calculate the below information:


              1. RAG status of Red > 1 month
              2. Amber status > 3 months
              3. Not Reported > 45 days, and(from database itself we are getting more than 45 days information for N/R)
              4. % = (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.