Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try using
count(distinct(total [Trade ID]) as denominator
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
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...