Median of several rows for each date in load script

[Madina]

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...

[MatheusC]

Hi, @Madina 

See if it meets your need

this is an example, via script

date:
load * Inline [
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
];

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

[Madina]

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?

[hic · Accepted solution]

Try

Load
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];

[Madina]

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)

[hic]

That could work, yes. But I think it would be better to use RangeMin() or RangeMax() for this. There is a whole set of Range functions that can calculate statistics for multiple values on the same row. See

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/RangeFu...

[Madina]

Oh, I see. Thank you !