3 Replies Latest reply: Jun 10, 2015 12:14 PM by Martijn Noorda RSS

    count function in graphs

    Martijn Noorda

      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
          santhosh k

          Try using

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

          • Re: count function in graphs
            David Sugito

            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
                Martijn Noorda

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