Using Fractile Function in IF Statement

[andrewroda]

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?

[ogautier62]

Hi,

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

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

regards

[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?

[bme]

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.

[andrewroda]

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))

[Anonymous]

Hi Andrew

I have used this to get the average of each quartile which is using the fractile as a filter in the set expression. Not sure if that will help you.

=avg({<MyRow={"<=$(=fractile(total MyRow,.25))"}>}MyRow) 

There seems to be a bug that it ignores current selections of the field you're averaging, but I got around it by forcing the top value when averaging the other quartiles:

=avg({<MyRow={">=$(=fractile(total R,.5))<$(=fractile(total MyRow,.75))"}>}MyRow) 

=avg({<MyRow={">=$(=fractile(total MyRow,.75))<=$(=max(total MyRow))"}>}MyRow) 

I have included the app and shown where it goes wrong.

!