Calculating Median!

[Chari24]

Hello All,

Can you please help me with below.

I have a data set like below

Id,  check1, check2, check3

Aaa,  1,0,0

Bbb, 1,1,1

Ccc,  0,0,0

Ddd, 1,0,0

EEE,0,0,0

FFF,1,0,0

Id, date1, date2

Aaa, 1/2/2025, 10/11/2025

Bbb, 1/2/2025, 8/10/2025

Ccc, 4/2/2025, 10/12/2025

Ddd, 4/2/2025, 9/11/2025

 EEE, 10/3/2025, 10/5/2025

FFF, 10/13/2025, 10/15/2025

Now I want to use  set analysis or any other way  in Qlik to show in a text box or table  median of days between Date1 and  Date2 for ID which has has 1 in check1 , check2 and check3

And also other text box showing median where check1 , check2 and check3 is 0

for example when user select October (Date1) I want to show two boxes - median days between date1 and date2 for EEE where all cehcks are 0 and one for FFF where it has attest one check 1.

Thank you for your support!

[Lisa_P]

I created two KPI objects, but you could use a text and image object

This is the expression for check = 1

{<check1={1}, check2={1}, check3={1}>}Median(date2 - date1)

[Amit_Prajapati]

Hi @Chari24,

You can use the Interval(EndDate - StartDate, 'hh:mm:ss') function to calculate the time difference between two dates. To apply this in the backend, perform a left join on Id after calculating the interval.

Additionally, you can create a flag where any of the check fields equal 1, or use a set expression to filter those records efficiently.

CheckFlags:
LOAD * INLINE [
Id, check1, check2, check3
Aaa, 1, 0, 0
Bbb, 1, 1, 1
Ccc, 0, 0, 0
Ddd, 1, 0, 0
EEE, 0, 0, 0
FFF, 1, 0, 0
];

DateRanges1:
LOAD * INLINE [
Id, date1, date2
Aaa, 1/2/2025, 10/11/2025
Bbb, 1/2/2025, 8/10/2025
Ccc, 4/2/2025, 10/12/2025
Ddd, 4/2/2025, 9/11/2025
EEE, 10/3/2025, 10/5/2025
FFF, 10/13/2025, 10/15/2025
];

left join(CheckFlags)
DateRanges:
load *,
num(Interval(date2-date1)) as DateDiff
Resident DateRanges1;

drop Table DateRanges1;