Fractile() - Function not working in set analysis

TinaHensel — Thu, 22 Apr 2021 07:39:55 GMT

Hello,

i am having an issue in using fractile-function in set analysis. The following statement including set analysis is resulting in 0.

Count ( ${ < seconds = {"(>=Fractile(seconds, 0.99))"} > } user_id )

Whereas the following statement is working, but is really really slow:

Count ( if ( seconds >= Fractile(TOTAL seconds, 0.99) , user_id ) )

Any ideas on how to fix this?

Thank you!

Re: Fractile() - Function not working in set analysis

marcus_sommer — Thu, 22 Apr 2021 15:21:39 GMT

The set analysis syntax isn't quite right and should be look more like:

Count ( ${ < seconds = {">=$(=Fractile(seconds, 0.99))"} > } user_id )

whereby the now per $-sign expansion created adhoc-variable is calculated globally before the chart the calculated and applied to each row - means you couldn't use such logic if it needs to consider the dimensionality of your object. But because of your applied TOTAL within the if-loop which ignores all dimensionality it may be suitable for your use-case.

If not you may change it to something like this:

Count ( ${ < user_id = {"=seconds>=Fractile(TOTAL seconds, 0.99)"} > } user_id )

which forced an evaluation on row-level but technically it's quite the same like as an if-loop and therefore it shouldn't be a big difference in regard to the performance - whereby I didn't measured it directly against each other.

Beside this your here shown expression for the if-loop didn't look right respectively completed because it's a nested aggregation which isn't allowed without using an aggr(). But you may change it to:

if(seconds >= Fractile(TOTAL seconds, 0.99), Count(user_id))

which could avoid the nesting aggregation and the logic:

if(condition, sum/count(field))

is in general much more performant as:

sum/count(if(condition, field))

If all those approaches didn't work or are further too slow you will need to look to optimize your datamodel to ensure that all relevant fields comes from a single table.

- Marcus

topic Re: Fractile() - Function not working in set analysis in App Development

Fractile() - Function not working in set analysis

Re: Fractile() - Function not working in set analysis