topic Re: Count function in graph in QlikView

Count function in graph

Fri, 16 Jun 2017 10:06:39 GMT

I used an expression in a graph as follows:

Count({<[Tijdigheid]={'Op tijd'}>} DISTINCT [Transaction No.]) /count([Transaction No.])

It counts transactions that were booked on time and divides them by the total transactions to derive a percentage. I used portfolio as a dimension.

It seemed to be working fine, but when I selected 'Te laat' (Too late) from the Field 'Tijdigheid' (timeliness), the percentages of timely booked transactions spike. I would expect that when only 'Too late' Transactions are selected, the percentage of timely booked transactions would be zero, since only 'Too late' booked transactions are selected. However, the function count([Transaction No.], counts only those transactions that are selected (the transactions that are too late), where as Count({<[Tijdigheid]={'Op tijd'}>} DISTINCT [Transaction No.]), counts all timely booked transactions within the portfolio, no matter the selection in the field 'Tijdigheid'.

This is not necessarily a problem, since the main objective of this graph is to present the percentage of timely booked transactions. However, I am looking for an explanation, because I fail to understand why the part of the function in the numerator of my function does not change based on the selection, where as the result of the function in the denominator does change. Can anyone please explain this to me?

Thanks in advance!

Re: Count function in graph

niclaz79 — Fri, 16 Jun 2017 10:47:32 GMT

Hi Martin,

The reason that the numbers changed is that the selection you made impacts the count([Transaction No.]) that you are dividing with. If you use set analysis in this one to (i.e. count({<[Tijdigheid] = >} [Transaction No.]) it should stay the same.

Also, is it intentional to use count distinct in just one of the numbers but not the other? Seems like you are dividing apples with oranges like that.

Re: Count function in graph

Kushal_Chawda — Fri, 16 Jun 2017 11:04:04 GMT

as you have used set analysis on timeliness field in Numerator, it will not change based on any other selection in that field, to do that use below expression

Count({<[Tijdigheid] *={'Op tijd'}>} DISTINCT [Transaction No.]) /count([Transaction No.])

Re: Count function in graph

Fri, 16 Jun 2017 11:58:28 GMT

Thanks Kushal, this worked perfectly

Re: Count function in graph

Mon, 19 Jun 2017 09:56:52 GMT

Hi Niclas,

Thanks for the advice! I should have left the 'distinct' out of the expression, since I used a script that only selects unique transaction numbers. So yes, it would be like dividing apples with oranges, but in this case it does not make any difference.