Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dirk_fischer
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.

Already in advance, thank you very much for your support.

Best regards

Dirk

TABLE_AVG.jpg

1 Solution

Accepted Solutions
dirk_fischer
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
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?

dirk_fischer
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

DirkTABLE_COMBINED.jpg

MVP
MVP

Re: Weighted meridian in a straight table

What is the expression used here?

dirk_fischer
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
MVP

Re: Weighted meridian in a straight table

What about the 25% Quantile?

dirk_fischer
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
MVP

Re: Weighted meridian in a straight table

marcowedel

Just want to get your attention here

MVP
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).

dirk_fischer
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