New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

Weighted meridian in a straight table

Hi experts,

I try to apply a formula for calculating the median of weighted values (like in the post from Matt King Weighted median) in a straight table and I want to apply the formula from Marco Wedel to all dimension values in the table. I was able to calculate it for the average, because there you don't Need to Aggregate. Now I want to calculate the median for FORMAT TOP, CUT BOTTOM, ....

If I select a mesurement (NAME), then the calculation is working, but if I don't have anything selected, there is only 1 result for the whole table instead of one result per row.

I thought something like

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}

If(

((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

Sum(TOTAL HAEUFIGKEIT))>=0.5) And

((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

Sum(TOTAL HAEUFIGKEIT))<0.5),

ERGEBNIS

),

(ERGEBNIS,(NUMERIC,ASCENDING)),NAME

)

might do the trick. But it doesn't and  I would be happy, if I got some hints about how to handle this.

Best regards

Dirk

1 Solution

Accepted Solutions
Contributor

Re: Weighted meridian in a straight table

Hello everybody,

finally I found the solution for my Problem, based upon the design blog of HIC (thank you very much for this valuable explanation).

If somebody is looking for calculating the median or quantiles based upon pre-aggregated values and displaying the results in a straigt table (so you can have an overview of the results for different measurements), the following formula works for me.

Median:

Aggr({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>} NoDistinct

Only(If(Aggr({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>} NoDistinct

RangeSum(Above(Sum({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>}FREQUENCY),

0,RowNo())),RULE_NAME,(RESULT,(NUMERIC,ASCENDING))) /

Aggr({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>} NoDistinct

Sum({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>}FREQUENCY),RULE_NAME) >=0.5

And

Aggr({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>} NoDistinct

RangeSum(Above(Sum({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>}FREQUENCY),

1,RowNo())),RULE_NAME,(RESULT,(NUMERIC,ASCENDING))) /

Aggr({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>} NoDistinct

Sum({<RESULT_TYPE={'UNSIGNED_INT_7_BIT','SIGNED_INT_13_BIT'}>}FREQUENCY),RULE_NAME)<0.5,

RESULT)),

RULE_NAME)

Best regards,

Dirk‌

17 Replies
MVP

Re: Weighted meridian in a straight table

 If I select a mesurement (NAME), then the calculation is working, but if I don't have anything selected, there is only 1 result for the whole table instead of one result per row.

Can you show where you see 1 result for the whole table when you don't have anything selected?

Contributor

Re: Weighted meridian in a straight table

Hi Sunny,

please see attached image. If I try to calculate the 25 % quantile, I get the result for a different dimension. I assume, that the RowNo() function might be part of the problem.

Best regards

Dirk

MVP

Re: Weighted meridian in a straight table

What is the expression used here?

Contributor

Re: Weighted meridian in a straight table

Hi Sunny,

the Expression used for the median is

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}

If(

((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

Sum(TOTAL HAEUFIGKEIT))>=0.5) And

((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

Sum(TOTAL HAEUFIGKEIT))<0.5),

ERGEBNIS

),

(ERGEBNIS,(NUMERIC,ASCENDING))

)

Regards

Dirk

MVP

Contributor

Re: Weighted meridian in a straight table

Hi Sunny,

same structure, just a different limit value.

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}

If(

((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

Sum(TOTAL HAEUFIGKEIT))>=0.25) And

((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

Sum(TOTAL HAEUFIGKEIT))<0.25),

ERGEBNIS

),

(ERGEBNIS,(NUMERIC,ASCENDING))

)

MVP

Re: Weighted meridian in a straight table

Just want to get your attention here

MVP

Re: Weighted meridian in a straight table

It looks like you have two dimensions in your table. The Aggr() needs to include all the table dimensions as well, so perhaps

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}

If(

((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

Sum(TOTAL HAEUFIGKEIT))>=0.5) And

((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

Sum(TOTAL HAEUFIGKEIT))<0.5),

ERGEBNIS

),

CATEGORY, NAME

)

(I don't have QV12/Sense, so this would produce a QV11 type (unsorted) Aggr(). Just add the sort qualifier to the expression above if you need it).

Contributor

Re: Weighted meridian in a straight table

Hello Jonathan,

thank's for the hint. At least I get a result in every row of the table, even if the results are not correct (there is only one value > 0 and I'm perfectly sure, that 0 is not the median for the rest of the measurements).

We are using QlikSense 32 SR4, so I need to see, what's necessary for the next step.

Best regards

Dirk