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.




        • Re: Trend Analysis
          Arvind Kumar Jha

          I am using the below formula :




          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.




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