2 Replies Latest reply: Jul 26, 2018 5:40 PM by Juan Julian Herranz Sarrion RSS

    Set analysis date with count function

    Juan Julian Herranz Sarrion

      Hello everyone,

       

      I have built a KPI with the  "likelihood to recommend" value for any selected month.

       

      I am now trying to incorporate within the same KPI  the previous month  "likelihood to recommend"  value. However, I am running into problems.

       

      For my current KPI I have the following set analysis:

      num(Count({<LIKELIHOOD_TO_RECOMEND_TEXT={"Promoter"}>}LIKELIHOOD_TO_RECOMEND_TEXT)-Count({<LIKELIHOOD_TO_RECOMEND_TEXT={"Detractor"}>}LIKELIHOOD_TO_RECOMEND_TEXT))

      /num(Count(LIKELIHOOD_TO_RECOMEND_TEXT))*100

       

      For my previous month KPI I have tried the following two set analysis both without luck:

      1)

      num(Count({$1}{<LIKELIHOOD_TO_RECOMEND_TEXT={"Promoter"}>}LIKELIHOOD_TO_RECOMEND_TEXT)-Count{$1}({<LIKELIHOOD_TO_RECOMEND_TEXT={"Detractor"}>}LIKELIHOOD_TO_RECOMEND_TEXT))

      /num(Count({$1}LIKELIHOOD_TO_RECOMEND_TEXT))*100

       

      2)

      Sum({<MonthYear{'=$(vMaxPreviousMonthYear)'}>}num(Count({<LIKELIHOOD_TO_RECOMEND_TEXT={"Promoter"}>}LIKELIHOOD_TO_RECOMEND_TEXT)-Count({<LIKELIHOOD_TO_RECOMEND_TEXT={"Detractor"}>}LIKELIHOOD_TO_RECOMEND_TEXT))/num(Count(LIKELIHOOD_TO_RECOMEND_TEXT))*100)

       

      If anyone knows the right way to tackle this issue I will greatly appreciate it. Promoters and detractors are matched in the back end with its corresponding numbers.

       

      Thank you all!

        • Re: Set analysis date with count function
          Khalil Foudhaily

          =

          (

          num(

               Count(

                         {<

                         MonthYear{"=$(vMaxPreviousMonthYear)"},

                         LIKELIHOOD_TO_RECOMEND_TEXT={'Promoter'}

                         >}

                         LIKELIHOOD_TO_RECOMEND_TEXT

                         )

               )

          -

          num(

               Count(

                         {<

                         MonthYear{"=$(vMaxPreviousMonthYear)"},

                         LIKELIHOOD_TO_RECOMEND_TEXT={"Detractor"}

                         >}

                         LIKELIHOOD_TO_RECOMEND_TEXT

                         )

               )

           

          /

          num(

               Count(

               {<

               MonthYear{"=$(vMaxPreviousMonthYear)"}    //percentage should be calculated considering global total or previous month too?

               >}

               LIKELIHOOD_TO_RECOMEND_TEXT

                         )

               )

          )

          *100