Weighted meridian in a straight table

[dirk_fischer]

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

[sunny_talwar]

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]

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

[sunny_talwar]

What is the expression used here?

[dirk_fischer]

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

[sunny_talwar]

What about the 25% Quantile?

[dirk_fischer]

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

    )

[sunny_talwar]

marcowedel‌

Just want to get your attention here

[jonathandienst]

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]

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