8 Replies Latest reply: Feb 26, 2016 9:16 AM by David Guštin RSS

    Counting problem with rates

    David Guštin

      Hello.

      I hope somebody will help me with this problem.

       

      In the attached table I have to count paid premiums.

      But If the rate is not fully paid, it shall not be counted as paid that month. Only when it is finally settled, it shall be counted as paid that month.

       

      For example:

      in July we have two payments, but the first is not fully paid. So it has to be considered as paid in October when we have final payment. This is clear if we look at INVOICE_ID.

           

      IDDATEPAIDWAS_INVOICEDINVOICE_ID
      7310203.07.20153,024,845004838057
      7310214.07.201504,845004933732
      7310230.07.20154,8405004933732
      7310214.08.201504,845041077206
      7310207.09.20154,8405041077206
      7310214.09.201504,845041231027
      7310206.10.20151,8205004838057
      7310206.10.20154,8405041231027
      7310214.10.201504,845041371358
      7310221.10.20154,8405041371358
      7310213.11.201504,845041507020
      7310214.12.201504,845041688607
      7310216.12.20154,8405041507020
      7310228.12.20154,8405041688607

       

      If we count PAID, we get 8 using the formula: =Count( {$<PAID = {'>0'}  >}PAID ) :

       

          

      I want results like below
      MONTH_YEARCOUNT_PAIDMONTH_YEARCOUNT_PAID
      total: 8 total: 7
      jul-20152jul-20151
      avg-20150avg-20150
      sep-20151sep-20151
      okt-20153okt-20153
      nov-20150nov-20150
      dec-20152dec-20152