topic Re: Fractile function does not work on Qlikview server 12 in QlikView

Fractile function does not work on Qlikview server 12

samith_sgh — Tue, 30 Aug 2016 11:52:18 GMT

I have used following function in a qlikview dashboard which works fine in Qlikview Desktop 12, but when I publish it in the server I can only get a '-' for the fractile value.

=Fractile(Aggr((Sum(Charge)/Count(DISTINCT TicketNumber)),Resource),0.9)

Is it some compatibility issue in Qlikview server version 12?

Data set looks like this.

TicketNumber	Charge	Resource
125	$ 10.00	A
125	$ 15.00	A
125	$ 20.00	A
130	$ 25.00	B
135	$ 25.00	C
140	$ 45.00	A

I'm trying to get average charge amount per ticket no for each Resource and get 90th percentile of that average (when grouped by Resource)

Re: Fractile function does not work on Qlikview server 12

Anil_Babu_Samineni — Tue, 30 Aug 2016 12:15:39 GMT

Just try again and see luck.

Re: Fractile function does not work on Qlikview server 12

ToniKautto — Tue, 30 Aug 2016 12:19:58 GMT

More likely the data in your aggregation is different when you run the app on server side. Are you perhaps using Section Access causing a reduction on server?

For troubleshooting start by validating that each component in your expression returns the values that you expect.

Concatenate the Aggr() result to confirm the result are as expected; Concat(Aggr((Sum(charge)/Count(DISTINCT TicketNumber)),Resource),',')

Re: Fractile function does not work on Qlikview server 12

samith_sgh — Tue, 30 Aug 2016 13:54:30 GMT

Thank you for your suggestion to use concat. Following is the result.

Left side 2 text boxes have my original Fractile function. Right side box has the Concat function as you mentioned.

When opened in Qlikview Desktop V12:

How published version looks like in Qlikview server 12. (Opened in Google chrome)

Seems like a issue in Aggr() function. But both of these files has the same data set.

There's no section access on this document.

Re: Fractile function does not work on Qlikview server 12

ToniKautto — Tue, 30 Aug 2016 22:31:20 GMT

Troubleshooting of Aggr() is done by setting up a straight table to mimic the aggregation. In your case it would be as below.

Dimension: Resource

Expression: Sum(Charge)/Count(DISTINCT TicketNumber)

With two components in the expression, you might need to split the expression into two.

Dimension: Resource

Expression1: Sum(Charge)

Expression2: Count(DISTINCT TicketNumber)

Re: Fractile function does not work on Qlikview server 12

samith_sgh — Thu, 08 Sep 2016 17:07:49 GMT

Thank you Toni,

I did the trouble shooting like that and identified that it's a issue with Aggr() .

As per the previous trouble shoot

Concat(Aggr((Sum(charge)/Count(DISTINCT TicketNumber)),Resource),',')

did not work on the server published version.

So I aggregated the numerator and denominator separately with concat()..

And it works

In Qlikview server 12 (published version)

So the solution:

Aggr(Sum(charge),Resource) / Aggr(Count(DISTINCT TicketNumber),Resource) works fine in the Qlikvew server 12 published dashboard, but Aggr((Sum(charge)/Count(DISTINCT TicketNumber)),Resource) does not work.