1 Reply Latest reply: May 28, 2018 2:22 AM by Arvind Kumar Jha RSS

    Trend Analysis

    Arvind Kumar Jha

      Hello Team,

       

      What is the best possible way to calculate trend for the following :

       

      Dimension Drill Down : Year,Quarter,Month

      Measure : Attrition Rate

       

      Attrition Rate = sum of people leaving in current quarter / total headcount in the previous quarter

       

      The same applies for year and month as well.

       

      Kindly provide assistance.

       

      Regards,

      Arvind

        • Re: Trend Analysis
          Arvind Kumar Jha

          I am using the below formula :

           

          sum(Employee)/Above(count(Employee))

           

          The problem is with above it gives blank value for the first record in drilldown due to above function.

           

          I have attached my qvf for reference.

          When i do drill down on Year , one quarter returns null value even though it is not.

           

          On further drilldown one month returns null value.

           

          How to avoid null values?

           

          Kindly help on the same.

           

          Thanks,

          Arvind

            • Re: Trend Analysis
              Jonathan Dienst

              You can't sum the field Employee - I am sure that is not a numeric. How do you identify the people that have left? If you have or create some sort of flag to indicate the leavers, then you could say something like:

               

                   =Count({<Leaver = {1}>} Distinct Employee) / Above(Count(Distinct Employee))

                • Re: Trend Analysis
                  Arvind Kumar Jha

                  Hello Jonathan,

                   

                  Thanks for the same.I mistakenly wrote sum.

                   

                  I had used formula similar to above but the concern it shows null values.

                  Dimension - DrillDown : Year ,Quarter ,Month

                  Measure : Attrition Calculation

                   

                  It returns correct values but null for one of the columns.Say i have data for all months from 2016 to 2018 then null data for 2015 is acceptable as above function will not find any value.

                  But even for 2017 when i drill down one of the quarters show null value and month also shows null value for one column.

                   

                  How can it be fixed?

                   

                  Thanks in advance. !