Count of Days with Fractile function

[shyamcharan]

Hi Experts,

I have data which has the ID, week, dates in the week, the response time in seconds and minutes as shown below.

ID	Week	Date	ResponseTime_Secondds	Min
1	1	01-01-2017	819	13.65
2	1	01-01-2017	495	8.25
3	1	01-01-2017	768	12.8
4	1	01-01-2017	587	9.783333
5	1	01-01-2017	489	8.15
6	1	01-01-2017	510	8.5
7	1	01-01-2017	975	16.25
8	1	01-01-2017	638	10.63333
9	1	01-01-2017	872	14.53333
10	1	01-01-2017	814	13.56667
11	1	01-01-2017	554	9.233333
12	1	01-01-2017	216	3.6
13	1	02-01-2017	529	8.816667
14	1	02-01-2017	945	15.75
15	1	02-01-2017	241	4.016667
16	1	02-01-2017	761	12.68333

As shown in the attached QVW, chart 1 has the 50th percentile values calculated for each individual day using the Fractile function.

In chart 2, I would need to show the count of days in a week that had the 50th percentile value >= 10.5.

The expected result from the data in attached QVW should be Week 1 = 3 and Week 2 = 5 days.

However, I am unable to get it worked.

Can you please help. Appreciate your response.

Regards,

Shyam

[tresesco · Accepted solution]

Try expression:

Count(if (Aggr(Fractile(Min,0.5),Week,Date)>=10.5,Date))

Note: Set analysis doesn't work here, because your fractile calculation here is dimension sensitive, and set analysis is always dimension (in chart) insensitive.

[PrashantSangle]

aggr(Count({<Date={"=Fractile(Min,0.5)>=10.5"}>}DISTINCT Date),Week)

regards,

[Kushal_Chawda]

use below expression

=Count(DISTINCT {<Date={"=Fractile(Min,0.5)>10.5"}>}Date)

[Kushal_Chawda]

set analysis suggested by me is working in this scenario tresesco‌

[sunny_talwar]

I wonder if we can simplify from Fractile(..., 0.5) to Median ... Will it give the same output

Fractile(Min, 0.5) = Median(Min)??

[Kushal_Chawda]

We don't need Aggr here, because Week is already there as dimension dreamer4

[sunny_talwar]

Just for testing, I tried by modifying  tresesco‌'s expression and it seems to work for the sample... but I would want to test more

Count(if (Aggr(Median(Min),Week,Date)>=10.5,Date))

[Kushal_Chawda]

Yes 50 Percentile means median only. we can try that..

[sunny_talwar]

In general it is... but in Qlik is it true also? I am not 100% sure....