8 Replies Latest reply: Nov 5, 2018 10:36 AM by Isabel Encinas RSS

    Count Unique

    Isabel Encinas

      Hi Experts,

       

      Need advise here please.

       

      Scenario:

      one customer(formatted cli) called and answered 5 times by different agents (common user name). see below data.

      raw data.JPG

      Problem:

      1. Count the unique number of customer (formatted CLI) that was answered more than once but not greater than 20 and tag it as Repeat Callers.

      2. Count the unique number of customer (formatted CLI) that was answered only once and tag it as Single Callers.


      In-Progress:

      I've created an expression and Presented it thru Pivot

      # Repeat Callers = num(count(aggr(if(Sum([#Answered])>1,Sum([#Answered]), null()) ,[Formatted CLI]))  ,vFormatCount0)

      - num(count(aggr(if(Sum([#Answered])>20,Sum([#Answered]), null()) ,[Formatted CLI])) ,vFormatCount0)

      # Single Callers = num(count(aggr(if(Sum([#Answered])=1,Sum([#Answered]), null()) ,[Formatted CLI])) ,vFormatCount0)



      The above expression seems to be working when Formatted CLI is on the first dimension.

      formatted cli.JPG

      But when I change the Pivot and look for Common User Data, the expression is not showing the expected result.

      common user name.JPG

      Desired Result should be:

      Common User Name# Answered# Repeat Callers# Single Callers
      Am101
      Av101
      Mi101
      Sh101
      Sr101


       

      Is there anything wrong with my expression? Please advise.

        • Re: Count Unique
          Andrey Krylov

          Hi Isabel.Your expressions are correct but there should be added the dimensions to aggregate by. Try this with no aggr

          # Repeat Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])>1"}-{"=Sum([#Answered])<20"} >} Distinct [Formatted CLI]), vFormatCount0)

          # Single Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])=1"} >}Distinct [Formatted CLI]), vFormatCount0)

            • Re: Count Unique
              Isabel Encinas

              Hi Andrey,

               

              Thanks for your reply.

              I tried the expression you've given but still did not give me right result.

               

              '# Repeat Callers = num(Count({<[Formatted CLI] = {"=Sum([#Answered])>1"}-{"=Sum([#Answered])<20"} >} Distinct [Formatted CLI]), vFormatCount0)'

               

               

              repeat callers not correct total.JPG

              the # Repeat Callers* below is showing 5 on Alis (common user name), further drill down to customer (formatted cli) - see highlighted as sample.

              repeat callers by agent.JPG

              the customer 'formatted cli' is actually handled by 2 different agents with only 1 answered each.

              Alis should have 0 # Repeat Callers.

               

              Thanks

                • Re: Count Unique
                  Andrey Krylov

                  Oh, I see. Then it should be aggregated and maybe this

                  # Repeat Callers = num(Sum(if(aggr(Sum([#Answered]),[Formatted CLI], [Agent]) > 1 and aggr(Sum([#Answered]),[Formatted CLI], [Agent]) < 20, 1, 0)),vFormatCount0)

                  # Single Callers = num(Sum(if(aggr(Sum([#Answered]), [Formatted CLI], [Agent]) = 1, 1, 0)),vFormatCount0)

                  Agent is the agent field you mentioned

                    • Re: Count Unique
                      Isabel Encinas

                      Hi Andrey,

                      The customer (formatted CLI) '+612444' was answered by 2 different agents (common username).

                      raw fcr.JPG

                      After using the expression , # Repeat Callers = num(Sum(if(aggr(Sum([#Answered]),[Formatted CLI], [Agent]) > 1 andaggr(Sum([#Answered]),[Formatted CLI], [Agent]) < 20, 1, 0)),vFormatCount0),


                      It did not show the correct result.

                      should be 1 repeat caller


                      formatted cli2..JPG

                • Re: Count Unique
                  Andrea Gigliotti

                  maybe this:

                   

                  for repeat callers:

                  sum( aggr( if( Sum([#Answered]) > 1 and Sum([#Answered]) <= 20, 1, 0 ), [Common User Name], [Formatted CLI] ) )

                   

                  for single callers:

                  =sum( aggr( if( Sum([#Answered]) = 1, 1, 0 ), [Common User Name], [Formatted CLI] ) )

                   

                  I hope it helps.