topic Median for Table Total Row in QlikView

Median for Table Total Row

Jack21 — Sat, 16 Nov 2024 20:53:07 GMT

I require help in identifying Median for Table Total in Row (highlighted in Blue). Shown sample below. please provide the QVW sample file for this. it would be a great help. Thanks in advance.

CUR	A	B	C	D	E
USD	10.00	8.00	13.00	6.00	14.00
INR	58.00	56.00	61.00	54.00	62.00
EUR	62.00	60.00	65.00	58.00	66.00
CZK	35.00	33.00	38.00	31.00	39.00
Total	165.00	157.00	177.00	149.00	181.00

Median for the total 165.00

Re: Median for Table Total Row

Anil_Babu_Samineni — Mon, 13 May 2019 12:41:11 GMT

PFA

Re: Median for Table Total Row

Jack21 — Mon, 13 May 2019 14:42:01 GMT

Thanks a lot for timely response. I require median for expression columns total row. i have attached sample required QVW and input files. Could you please update Median text object with calculation in the attached QVW and provide. this would be great support.

Re: Median for Table Total Row

Jack21 — Tue, 14 May 2019 04:11:29 GMT

Can someone help on this. Please provide me the calculation in the attached Sample QVW.

Re: Median for Table Total Row

Anil_Babu_Samineni — Fri, 17 May 2019 12:46:51 GMT

@marcus_sommer Can you please help here?

Re: Median for Table Total Row

marcus_sommer — Fri, 17 May 2019 13:07:06 GMT

In this case you could use:

= rangefractile(0.5, SUM(ValueCur),
                                       SUM(ValueCur)-SUM(ValueCur)/4,
                                       SUM(ValueCur)-SUM(ValueCur)/3,
                                       SUM(ValueCur)-SUM(ValueCur)/2)

- Marcus

Re: Median for Table Total Row

Anil_Babu_Samineni — Fri, 17 May 2019 13:10:17 GMT

@marcus_sommer Does it mean that, there is no direct functionality. Because, Few of the users are getting many ADHOC reportss. Wil that work, If we use static expressions together? Just to endorse, Post your answer when you have time,

Re: Median for Table Total Row

Jack21 — Fri, 17 May 2019 13:30:41 GMT

Thank you so much @marcus_sommer

Re: Median for Table Total Row

marcus_sommer — Fri, 17 May 2019 14:14:12 GMT

In this case there is no direct range-median function - maybe because of the fact that rangefractile does also the job. In the end it might be necessary to adjust the datamodel to be able to apply the needed statistically functions. But there is no general approach which could be applied for all respectively most of such calculations else it depends always on the data (model) and the exact requirement.

I think it depends mostly on the understanding of the statistically methods itself - if this is rather weak (like by me and many other users which use such functions often like a light switch without a deeper knowledge of them) it could become quite hard to implement it within a datamodel.

- Marcus

Re: Median for Table Total Row

Jack21 — Mon, 20 May 2019 09:49:31 GMT

@marcus_sommer This median is calculating wrongly when out of 4 expressions, 1 expression is not selected and shown with null value. For example:

Program	I2I CD539A	I2I U625	I2I U540	I2I S650	Median
Total	$ 12.00	$ 15.00	$ 16.00	$ 18.00	$ 15.50	Correct
Total	$ 12.00	$ 15.00	$ 16.00	$ 0.00	$ 13.50	Wrong
	Wrong because it picks all 4 totals, 1 is not selected in list
Total	$ 12.00	$ 15.00	$ 16.00	$ 0.00	$ 15.00	Correct
	Correct because it has to pick only 3 expressions which are selected

Re: Median for Table Total Row

marcus_sommer — Mon, 20 May 2019 11:26:38 GMT

In this case you need to extend the logic to ensure that the expression without available data didn't return 0 else null(). Quite probably you will need a check like: if(exp = 0, null(), exp). Just try the logic within a textbox with:

RangeFractile(0.5, 12,15,16,0) vs. RangeFractile(0.5, 12,15,16,null())

- Marcus