topic Re: Count of Days with Fractile function in QlikView

Count of Days with Fractile function

shyamcharan — Tue, 12 Sep 2017 11:06:40 GMT

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

Re: Count of Days with Fractile function

tresB — Tue, 12 Sep 2017 11:16:59 GMT

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.

Re: Count of Days with Fractile function

PrashantSangle — Tue, 12 Sep 2017 11:17:16 GMT

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

regards,

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:19:12 GMT

use below expression

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

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:26:33 GMT

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

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:26:45 GMT

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

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

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:28:13 GMT

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

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:28:55 GMT

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

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:29:08 GMT

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

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:30:43 GMT

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

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:34:30 GMT

Should be calculation logic won't change in Qlik right?

Re: Count of Days with Fractile function

tresB — Tue, 12 Sep 2017 11:35:16 GMT

You are right while saying 'in this scenario'. Since, date is a dimension being used in the chart (or in aggr) and you use the same field in the set analysis( Date={"...), putting distinct works; again 'in this scenario'. . However, had there been more dimensions in aggr/chart and not in the resultant chart.. this would not work.

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:36:04 GMT

Pata nai yaar... I don't fully trust the Fractile function in Qlik.... they have a very unique way of calculating Percentile... whick is why I am hesitant to say that they are the same or not

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:36:56 GMT

But in that case you would have needed to modify your solution also, isn't it

Re: Count of Days with Fractile function

its_anandrjs — Tue, 12 Sep 2017 11:38:04 GMT

Check this about the Fractile from Qlik Help

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/StatisticalAggregationFunctionsinChar…

Regards

Anand

Re: Count of Days with Fractile function

shyamcharan — Tue, 12 Sep 2017 11:38:20 GMT

Thank you all for quick response.

Thank you Tresesco, Kushal and sunny. All your solutions work. Great!!

Re: Count of Days with Fractile function

sunny_talwar — Tue, 12 Sep 2017 11:40:35 GMT

Check how Fractile gets calculated in Qlik

What is the exactly calculation of the fractile function?

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:40:38 GMT

Yes, that is why I specified "this scenario"

Re: Count of Days with Fractile function

Kushal_Chawda — Tue, 12 Sep 2017 11:45:10 GMT

shyamcharan‌ I would suggest to use set analysis solution as it is always advised. If condition with Aggr impacts the performance of the chart (if data is huge, otherwise no problem).

Re: Count of Days with Fractile function

tresB — Tue, 12 Sep 2017 11:47:49 GMT

Yes, absolutely. I only wanted to establish - the set analysis insensitivity of chart dimension. And yes, kush141087‌'s solution is better 'in this scenario'.