How To Find Middle value in straight table

[qlikuser22]

Hi

I am trying to find the middle value in qliksense straight table. I am writing this expression, but this works in a static way. I want to make it dynamic. Any help. 

I have a column A with 6 values. 

A

10

23

11

34

12

12

if(Rowno(Total)=3,Above(Max(A),0),0) 

I have given the number 3 as the middle value. I want to make it dynamic like count the total no of rows and divide by 2. 

Any help please. 

Thanks in advance. 

[Andrei_Cusnir]

Hello,

I am not 100% sure that I understood correctly this use case scenario. Can you please provide an output example? For instance you can specify something like:

A, Output

10, 6/2

23, ...

11, ...

34, ...

12, ...

12, ...

Where 6 is the count of all rows divided by 2 etc. Just so we get the expected outcome from the expression. 

[vinieme12]

in script you can use  NoOfRows('TableName')  to return the no of rows in a table;

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/TableFu...

let middlerow = floor(NoOfRows('TableName')/2) ;

[qlikuser22]

Thanks for the quick reply @Andrei_Cusnir 

I am trying to use that expression in KPI. 

if(Rowno(Total)=3,Above(Max(A),0),0) - this should give me the answer 34.  

If the no of rows is 15 then it should be floor(middle row). 15/2 - 8th row and its value in KPI which is 54.

Example

A

1

2

3

4

21

23

45

54

21

34

12

32

76

18

19

Thanks. 

[qlikuser22]

Thanks @vinieme12  Will this expression with variable can be used in KPI also? 

Like, 

if(Rowno(Total)=middlerow ,Above(Max(A),0),0)

Thanks in advance. 

[HugoRomeira_PT · Accepted solution]

Following the logic you shared, for a straigh table try the following expression:

Below(A,round(NoOfRows(TOTAL)/2,1)-rowno())

*Replace A by your Column Name

In any case, to compare numeric values and to compare all values with the middle table value, this should work with the statistical function median(). This works in any scenario (both on tables, KPIs, charts).

Median(Total Distinct A)

*Replace A by your Column Name

See my example bellow, with the detailed calculations:

Hope it helps!

[qlikuser22]

Perfect @HugoRomeira_PT  this works very good for straight table. Will be able to use the same for KPI? I want to have the value for that row in the KPI box. 

Any help please!

[Andrei_Cusnir]

Hello @qlikuser22,

For a KPI, the solution posted by @vinieme12 is going to help you better. The solution posted by @HugoRomeira_PT works with Table chart because it is using functions like RowNo() and this function will not work properly in a KPI as KPI doesn't have rows like a Table does. In that case in a KPI RowNo() will always return the number 1. 

In the other solution, you load the data within Data load editor and you store it inside a variable middlerow. Then you can use the variable in the KPI as $(middlerow) to get the number displayed there. 

I hope that this information helps.

[HugoRomeira_PT]

The second option I gave you should work in any scenario (both on tables, KPIs, charts).

Median(Total Distinct A)

*Replace A by your Column Name

Median() returns the median value of the range of values aggregated in the expression iterated over the chart dimensions.

By definition median is "the middle number in a sorted list of numbers".

Thanks

Hugo Romeira

[Andrei_Cusnir]

Hello @HugoRomeira_PT, thanks for mentioning it. I only saw the first solution and missed the second one by accident.