topic Re: statistical aggregations - parts between frac 0.1 and 0.9 in QlikView

statistical aggregations - parts between frac 0.1 and 0.9

marcus_sommer — Fri, 11 Dec 2015 09:02:42 GMT

Hello,

I want do some statistical aggregations about the distribution from a certain datas over various time-periods. For this I use avg/median/fractile which are working fine. But now I need to sum/avg parts of this, for example the 80% which are between fractile(sum(value), 0.1) and fractile(sum(value), 0.9). Any ideas how this could be solved?

- Marcus

Re: statistical aggregations - parts between frac 0.1 and 0.9

tresB — Fri, 11 Dec 2015 09:09:13 GMT

Not sure if understood right. Is it somehow close to this: Show Bottom 5 in Bar Chart?? ?

Re: statistical aggregations - parts between frac 0.1 and 0.9

Kushal_Chawda — Fri, 11 Dec 2015 10:01:57 GMT

may be this will help

Fractile / Quartile in pivot table dimension

Fractile/Aggr/Sum command in load statement

Calculating fractile() across a set dimension regardless of selections‌

Re: statistical aggregations - parts between frac 0.1 and 0.9

marcus_sommer — Fri, 11 Dec 2015 10:05:50 GMT

It goes in the right direction but don't worked for me. My case is a bit more complicated then I need to use aggr within the set analysis and it seems that comparing one aggr-result against another aggregation isn't possible:

avg({< F2 = {"=avg(aggr(sum(F1), F2))>fractile(sum(F1), 0.9)"}>} F1)

don't worked although both expression-parts are working alone and with comparing against a fixed value the expression worked:

avg({< F2 = {"=avg(aggr(sum(F1), F2))>1000"}>} F1)

- Marcus

Re: statistical aggregations - parts between frac 0.1 and 0.9

marcus_sommer — Fri, 11 Dec 2015 10:27:49 GMT

Unfortunately it didn't worked.

Re: statistical aggregations - parts between frac 0.1 and 0.9

Kushal_Chawda — Fri, 11 Dec 2015 10:32:26 GMT

is it possible to create the below Aggregation in Script

1) avg(aggr(sum(F1), F2))

2) fractile(sum(F1), 0.9)

Compare 1) & 2) in Script & create Flag then finally you can do avg({<Flag={1}>}F1)

Re: statistical aggregations - parts between frac 0.1 and 0.9

marcus_sommer — Fri, 11 Dec 2015 10:39:56 GMT

Script pre-calculations aren't possible - it needs to be flexible to selections like periods, areas and categories and also the fractile-parts should be per variable eligible.

Re: statistical aggregations - parts between frac 0.1 and 0.9

tresB — Fri, 11 Dec 2015 10:54:38 GMT

I would try with IF and aggr(), rather than Set Expression.

Re: statistical aggregations - parts between frac 0.1 and 0.9

marcus_sommer — Fri, 11 Dec 2015 12:28:31 GMT

Many thanks for your help. The set analysis approach is the better one and in my expressions was only a TOTAL missing, so that this worked:

avg({< F2 = {"=avg(aggr(sum(F1), F2))>fractile(TOTAL sum(F1), 0.9)"}>} F1)

- Marcus