Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Median of several rows for each date in load script

Hello,

I have a table which looks like this

dateid ,  a,  s, d,  r, f

1.1.2023,  1, 2, 3, 2, 5

2.1.2023 , 1,1, 2, 3, 4

and etc.

what i want is to get a table, where it calculates median on each date between other rows. Table like this

dateid, medians

1.1.2023, 2

2.1.2023, 2.

i mean for example on date 1.1.2023 there are five values :  1,2,3,2,5. And median is 2.
on date 2.1.2023 there are next five values: 1,1,2,3,4. And median between them is 2.
i need to get this table in data load scripts. Hope I could explain well what i want

#Update: The problem is solved, moreover, In solution below of @hic  if you change 0.5 on 0 you can get min per row, and if change 0.5 to 1  gives you  max per row

#Update 2  Was suggested to use for min and max  RangeMin() and RangeMax() . Link is below if interested
https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/RangeFu...

Labels (5)

• ### Script

1 Solution

Accepted Solutions
Former Employee

Try

Date#(dateid,'D.M.YYYY') as dateid,
a, s, d, r, f,
RangeFractile(0.5, a, s, d, r, f) as Median
Inline
[dateid , a, s, d, r, f
1.1.2023, 1, 2, 3, 2, 5
2.1.2023 , 1,1, 2, 3, 4
3.1.2023, 1, 2, 5, 8, 10];

6 Replies
Specialist

See if it meets your need

this is an example, via script

date:
Date, sales
1.1.2023,1
1.1.2023,2
1.1.2023,3
1.1.2023,2
1.1.2023,5
2.1.2023,1
2.1.2023,1
2.1.2023,2
2.1.2023,3
2.1.2023,4
];

Date,
median(sales) as medianSales
Resident date Group by Date;

Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
Contributor III
Author

Hello, @MatheusC

Thank you for yoour reply. The thing is that I have several columns, like a,  s, d,  r, f

Therefore, need median between all of them

In your case it takes median only for one column per each date

Or you mean rewrite table like one column and then find median?

Former Employee

Try

Date#(dateid,'D.M.YYYY') as dateid,
a, s, d, r, f,
RangeFractile(0.5, a, s, d, r, f) as Median
Inline
[dateid , a, s, d, r, f
1.1.2023, 1, 2, 3, 2, 5
2.1.2023 , 1,1, 2, 3, 4
3.1.2023, 1, 2, 5, 8, 10];

Contributor III
Author

Hello, @hic , thank you very much for your reply. I tried your solution. It did help. Thank you very much.

By the way, I used your way of solution to find min, max of rows too. Like if you change 0.5 on 0 in your expression, it shows min value for the row, and put 1 instead of 0.5 it shows max value.

Therefore, you solved my three problems. Thank you again)