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,

       

      a.ID

      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.

       

      Thanks,

      Sri.

        • 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.

           

          Cheers,

          Joyce

          • 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.

             

            thanks,

            Sri