4 Replies Latest reply: May 29, 2018 9:47 AM by Andrew Roda RSS

    Using Fractile Function in IF Statement

    Andrew Roda

      Hello,

       

      I am trying to write a set analysis expression that compares an individual user's avg(duration) the 1st,2nd,3rd, etc quartiles and assigns a value based on the comparison. For example:

       

      if the avg(duration) < 1st quartile the score would be 1

      avg(duration) > 1st quartile and <2nd quartile would be 2 and etc.

       

      I have calculated the quartiles for the overall data set using fractile(duration, .25) as 1st quartile, etc. So far the if statement I have written looks like this:

       

      IF( avg(duration) < fractile(TOTAL duration,.25),1,4)

       

      Individual user values that are less than the 1st quartile of the total set still populate as 4, which suggests that the set analysis is comparing the avg of the entire dataset to the 1st quartile of the entire data set. I have also tried to wrap the avg in aggr by the User dimension but still cannot get it to work.

       

      Has anyone successfully implemented a similar set analysis expression?

        • Re: Using Fractile Function in IF Statement
          Olivier GAUTIER

          Hi,

           

          your expression seems to be correct, if you have Users in dimension

           

          is that you have ? what are dimension and expression exactly ?

           

          regards

            • Re: Using Fractile Function in IF Statement
              Ben Myers

              Oliver is right, the expression is correct for assigning the value if User is a dimension.

               

              What you're describing though is not a behavior of set analysis. That is just a "filter" at the expression level and wouldn't assign any values.

               

              The Aggr function is what you would need here. You need to match your Aggr dimensions to the external ones on your table. Try something like this:

               

              Sum(Aggr(IF( avg(duration) < fractile(TOTAL duration,.25),1,4),User,Department))


              This expression would first calculate the "rating" and the user + department level. Then if the chart it's used in only has Department as a dimension then it would sum the "rating" for users in that department. The most common mistake with Aggr (aside from using it when it's not needed) is forgetting that it's virtual table that gets passed to attach to the dimensions on the chart object. The virtual table is not attached to the rest of the data in your app. In this example if Department wasn't included in the Aggr portion there would be no way to accurately assign users to the dimension in the chart.

                • Re: Using Fractile Function in IF Statement
                  Andrew Roda

                  Thanks, Ben.

                   

                  So far your addition to my expression works with what I am trying to do. My next step is trying to use this expression to assign values to the other tiers.

                   

                  For example, I would like to have a system that assigns 1 to the the avg(duration) < .fractile(duration,.25) and then 2 to the avg(duration) between fractile(duration, .25) and fractile(duration, .50) and then follow the same pattern assigning 3 and 4.

                   

                  I am not too familiar with Aggr and am not having much luck expanding this expression to include multiple ratings. Do you have any suggestions for this?

                   

                  I am going to keep playing around with the expression but so far I have tried:

                   

                  Sum(Aggr(IF( avg(CallDurationSeconds) < fractile(TOTAL CallDurationSeconds,.25),1,

                  IF( avg(CallDurationSeconds) >= fractile(TOTAL CallDurationSeconds,.25) and avg(CallDurationSeconds) < fractile(TOTAL CallDurationSeconds,.50),2),

                  4),%UserID,SalesTeamID))

              • Re: Using Fractile Function in IF Statement
                Sunny Talwar

                Would you be able to share few rows of sample data and explain what exactly are you looking to get as an output?