Using an Aggr function in a variable

[skustes]

We are building an app with a bunch of KPIs using Measures with aggregations. It works, but it's slow and there is a lot of redundancy in the Measures. So I'm trying to put the aggregations into variables and call the variables in my Measures so that each aggregation is made only once.

In this example, I am aggregating the count of type1 completions by user, then getting a count of users with 1 or more of those and dividing it by the total user count. In other KPIs, we'll use the exact same aggregation, but divide those with 2 or more by those with 1 or more to get a look at "how many users that do 1 go on to do more than 1".

If I use this as the Measure expression, I get the expected outcome (77.6%):

=sum(if(aggr(count({$<status={'Completed'}, type={'type1'}>} thing), user) >= 1, 1, 0)) / user_count

If I instead put the aggr(...) part in a variable "grp_user_comp_t1" and call that like this I get 0.0%:

=sum(if(grp_user_comp_t1 >= 1, 1, 0)) / user_count

 The variable definition is:

=aggr(count({$<status={'Completed'}, type={'type1'}>} thing), user)

Is it possible to create a variable with an aggregation like this? New to Qlik, so feel free to offer a better way to accomplish this goal.

[marcus_sommer · Accepted solution]

If your variable-statement is starting with an = it would be the equivalent to the LET statement in the script and mean that the content is evaluated before the result is assigned to the variable. This result would be invalide within the context of your variable-call.

Further I suggest to consider to call all variables with a $-sign expansion like $(var) or '$(var)' because it makes it more clear what happens - with single-quotes the content will be always a string and without it's a number or depending of the context also a string.

Beside this you won't improve the performance of your dashboard by outsourcing the expression-string into a variable. If you could calculate anything globally you could assign this result to a variable and re-using this variable-value would improve the performance but as far as you need to consider any further object- and/or selection context you would need to apply the expression-string which would behave as if there were written directly.

If you want to improve the performance you should check if you really need the aggr() constructs and the if-loops, maybe with something like this:

count({$<status={'Completed'}, type={'type1'}>} thing)  /
count({$<status={'Completed'}, type={'type1'}>} distinct TOTAL user)