Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Madina
Contributor III
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...

Regards, Madina
Labels (5)
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

6 Replies
MatheusC
Specialist
Specialist

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;

MatheusC_0-1699447310553.png

 



Did you find a solution to your question? Mark the solution as accepted and if you found it useful, press the like button!
Madina
Contributor III
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?

Regards, Madina
hic
Former Employee
Former Employee

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
Contributor III
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)

Regards, Madina
hic
Former Employee
Former Employee

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
Contributor III
Contributor III
Author

Oh, I see. Thank you !

Regards, Madina