Fractile() - Function not working in set analysis

[TinaHensel]

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!

[marcus_sommer · Accepted solution]

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