17 Replies Latest reply: Jul 4, 2018 2:49 AM by Dirk Fischer

# 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

• ###### 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?

• ###### 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

• ###### Re: Weighted meridian in a straight table

What is the expression used here?

• ###### 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

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

)

• ###### 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).

• ###### 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

• ###### Re: Weighted meridian in a straight table

marcowedel

Just want to get your attention here

• ###### Re: Weighted meridian in a straight table

Hi,

can you please post a sample application we can test possible solutions with?

thanks

regards

Marco

• ###### Re: Weighted meridian in a straight table

Hi Marco,

I prepared a sampe qvf, but I'm not sure how to upload it. I uploaded document in the first step. Can you tell me what do I have to do to attach it to a reply?

Thank you very much for your support.

Best regards,

Dirk

• ###### Re: Weighted meridian in a straight table

Click on "use advanced editor" in the upper right corner and "Attach" in the lower right corner of the editor.

hope this helps

regards

Marco

• ###### Re: Weighted meridian in a straight table

Hi Marco,

thank you very much. Now I was able to attach the sample data, I extracted.

Thank you very much for your help.

Best regards

Dirk

• ###### Re: Weighted meridian in a straight table

Hi Marco, hello experts

I don't know, if you have time to look further into this,  but I managed to get the rangesum working for the different results in the table. Formula below provides thise results.

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

Sum(HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY)

,0,RowNo()

)

)

However, if I try to calculate in the same table the sum of HAEUFIGKEIT the errors, it returns only one result. This seems to be in the middle of the available results.

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

ERROR,CATEGORY)

If I use the same formula in a table, where ERGEBNIS  is not part of the dimensions, the formula provides the desired result. But if I combine both formulas, null is returned.

Any suggestions, how to overcome this problem? In principle I need to aggregate per error and category and ignore the grouping for the ERGEBNIS in second part of the formula, where the aggregation must be per ERGEBNIS in the first part of the formula.

If anybody has a suggestion for a solution, this would be phantastic.

Best regards

Dirk

• ###### Re: Weighted meridian in a straight table

Hi experts,

I'm one step closer to the solution, but not there yet and maybe somebody has an idea, how to go the final step.

In the straight table showing the results for each meaurement, this provides me the Meridian. But if I try to apply it to a table, where the different results are not displayed, the function returns only Null.

So I believe to have it working in the reduced table, I ned to do some additional Aggregation, but how??

Any input would be very much appreciated.

Regards

Dirk

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

Sum(HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

0,RowNo()

)

)

/

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

Sum(HAEUFIGKEIT),

ERROR,CATEGORY))

>=0.5) And

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

Sum(HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

1,RowNo()-1

)

)

/

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

Sum(HAEUFIGKEIT),

ERROR,CATEGORY))

<0.5),

ERGEBNIS

)

• ###### Re: Weighted meridian in a straight table

Hello experts,

accidently I got one step closer to the target, but I'm not there and maybe somebody has an idea,what's the reason for the behaviour I get.

Now I use the 1st formula for calculating the median from my weighted data and the 2nd formula for the calculation of the 25 % quartile.

MEDIAN:

If((RangeSum(Above(Aggr(NoDistinct

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

0,RowNo()

)

)

/

Max(Aggr(NoDistinct

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

ERROR,CATEGORY))

>=0.5) And

(RangeSum(Above(Aggr(Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

1,RowNo()-1

)

)

/

Max(Aggr(NoDistinct

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

ERROR,CATEGORY))

<0.5),

ERGEBNIS,0

)

25% QUARTILE.

If((RangeSum(Above(Aggr(NoDistinct

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

0,RowNo()

)

)

/

Max(Aggr(NoDistinct

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

ERROR,CATEGORY))

>=0.25) And

(RangeSum(Above(Aggr(Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>}HAEUFIGKEIT),

(ERGEBNIS,(NUMERIC,ASCENDING)),ERROR,CATEGORY),

1,RowNo()-1

)

)

/

Max(Aggr(NODISTINCT

Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}>} HAEUFIGKEIT),

ERROR,CATEGORY))

<0.25),

ERGEBNIS,0

)

The Image below shows, the results, if I have a dimension ERGEBNIS in the table. Of course, I would like not to have the Dimension ERGEBNIS and I would like to have only one row per ERROR. If I remove the Dimension ERGEBNIS, I don't get any datasets displayed in the table anymore.

So the formula is close, but not fully what I need. I tried to put a Max(Aggr(,CATEGORY,ERROR) around my IF statement, but this returns only 0.

Does anybody have a suggestion how to get the rid of the multiple rows for each error?

Best regards,

Dirk

• ###### Re: Weighted meridian in a straight table

Hello everybody,

finally I found the solution for my Problem, based upon the design blog of HIC Recipe for a Pareto Analysis &amp;amp;amp;#8211; Revisited (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