Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

count function in graphs

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!

3 Replies
Contributor II

Re: count function in graphs

Try using

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

Not applicable

Re: count function in graphs

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

Not applicable

Re: count function in graphs

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

Community Browser