Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Jack21
New Contributor II

Median for Table Total Row

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.

CURABCDE
USD10.008.0013.006.0014.00
INR58.0056.0061.0054.0062.00
EUR62.0060.0065.0058.0066.00
CZK35.0033.0038.0031.0039.00
Total165.00157.00177.00149.00181.00

Median for the total 165.00

 

 

Labels (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Median for Table Total Row

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

10 Replies

Re: Median for Table Total Row

PFA

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Jack21
New Contributor II

Re: Median for Table Total Row

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.

Jack21
New Contributor II

Re: Median for Table Total Row

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

Re: Median for Table Total Row

@marcus_sommer Can you please help here?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP & Luminary
MVP & Luminary

Re: Median for Table Total Row

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

@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,

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Jack21
New Contributor II

Re: Median for Table Total Row

Thank you so much @marcus_sommer 

MVP & Luminary
MVP & Luminary

Re: Median for Table Total Row

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

Jack21
New Contributor II

Re: Median for Table Total Row

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

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