topic Re: Find middle value in a column and pick the other corresponding values in App Development

Find middle value in a column and pick the other corresponding values

qlikuser22 — Thu, 21 Apr 2022 08:21:55 GMT

I have a table of date fields and the difference between dates are calculated.

Step 1: I want to sort the values in (Completed - Started).

Step 2: I want to find the middle of (Completed - Started) and just pick the corresponding values from other columns(like Request-Started, Request - working, working-completed).

Example:

Middle(Completed-started) = 15

Request-Started = 14

Request-Working = 0

Working - Completed = 1

I need these results as a KPI.

Is this possible using qlik expression. ?

I tried using only expression, but it didn't help.

Any help.

Thanks in advance.

Re: Find middle value in a column and pick the other corresponding values

vinieme12 — Fri, 22 Apr 2022 03:28:47 GMT

please post some sample data

Re: Find middle value in a column and pick the other corresponding values

qlikuser22 — Fri, 22 Apr 2022 07:06:04 GMT

Hi @vinieme12

The above image is the sample data. It's exactly the same case. Please do help.

Re: Find middle value in a column and pick the other corresponding values

vinieme12 — Sat, 23 Apr 2022 05:08:39 GMT

please post it in excel

Re: Find middle value in a column and pick the other corresponding values

qlikuser22 — Sat, 23 Apr 2022 17:13:25 GMT

Hi @vinieme12

Thanks for the reply. Please find the attached excel.

Thanks in advance!

Re: Find middle value in a column and pick the other corresponding values

HugoRomeira_PT — Fri, 20 May 2022 15:38:04 GMT

Hello @qlikuser22

In this specific scenario I believe you can use the FirstSortedValue() function:

Example using your data set:

FirstSortedValue([Completed - Started], [Completed - Started] ,round(Count(distinct ID)/2,1))
Note: Change the [Completed - Started] with the values you which to retrieve.

See the result bellow:

Hope it helps!