topic Re: count function in graphs in QlikView

count function in graphs

Wed, 10 Jun 2015 13:55:06 GMT

I am using count functions to display ratios in a dashboard. The expressions seem to be ok, but when I click on a certain value, 1 meter should display a ratio of 1 whereas all others should by definition be zero. The fields that I use for the ratios are called [Trade ID] and [ESMA Match Status].

Taking these two ratios and selecting only those trades that are Pending, I still get a ratio that is larger than 0 for the category 2 mismatch ratio. This should be impossible by definition, since I selected the positions for which ESMA Match Status was equal to "PENDING". Please see the ratios and their respective expressions below.

Pending Trades/Total Trades

Category 2 Mismatch/Total Trades

These are my definitions:

Pending Trades ratio:

=count({$<[ESMA Match Status]={"PENDING"}>} distinct [Trade ID])/count(distinct([Trade ID]))

Category 2 mismatch ratio:

=count({$<[ESMA Match Status]={"UNMATCHED2"}>} distinct [Trade ID])/count(distinct([Trade ID]))

Could anybody assist in finding the cause of this issue?

Many thanks!

Re: count function in graphs

santhoo_san — Wed, 10 Jun 2015 14:07:43 GMT

Try using

count(distinct(total [Trade ID]) as denominator

Re: count function in graphs

Wed, 10 Jun 2015 14:54:28 GMT

Hi Martijn,

As you said, the 1st formula should be:

Pending Trades / Total Trades

Then, the expression should follow:

=COUNT( {$<[ESMA Match Status]={"PENDING"}>} DISTINCT [Trade ID] ) / COUNT( DISTINCT TOTAL [Trade ID] )

The same with the 2nd:

Category 2 Mismatch / Total Trades

Should be:

=COUNT( {$<[ESMA Match Status]={"UNMATCHED2"}> } DISTINCT [Trade ID] ) / COUNT ( DISTINCT TOTAL [Trade ID] )

Hope helps,

David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: davidshuang.com

Re: count function in graphs

Wed, 10 Jun 2015 16:14:13 GMT

Thanks for your reply!

The problem does not seem to be caused by the expression in the denominator; adding total does not yield a difference in trade count. Regardless of the value of the denominator (unless it's zero), the ratio should be zero for the value "UNMATCHED2" when I select "PENDING", since the count in the numerator should then by definition be zero, since a trade cannot be pending and unmatched2 at the same time. I just do not see how I can make a selection that rules out the other value, but the expression still results in a positive count of that value...