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

Counting problem with rates

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.

 ID DATE PAID WAS_INVOICED INVOICE_ID 73102 03.07.2015 3,02 4,84 5004838057 73102 14.07.2015 0 4,84 5004933732 73102 30.07.2015 4,84 0 5004933732 73102 14.08.2015 0 4,84 5041077206 73102 07.09.2015 4,84 0 5041077206 73102 14.09.2015 0 4,84 5041231027 73102 06.10.2015 1,82 0 5004838057 73102 06.10.2015 4,84 0 5041231027 73102 14.10.2015 0 4,84 5041371358 73102 21.10.2015 4,84 0 5041371358 73102 13.11.2015 0 4,84 5041507020 73102 14.12.2015 0 4,84 5041688607 73102 16.12.2015 4,84 0 5041507020 73102 28.12.2015 4,84 0 5041688607

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

 I want results like below MONTH_YEAR COUNT_PAID MONTH_YEAR COUNT_PAID total: 8 total: 7 jul-2015 2 jul-2015 1 avg-2015 0 avg-2015 0 sep-2015 1 sep-2015 1 okt-2015 3 okt-2015 3 nov-2015 0 nov-2015 0 dec-2015 2 dec-2015 2
• Re: Counting problem with rates

hic Would you be so kind and take a look at this problem and tell me if is possible?

• Re: Counting problem with rates

Try this:

TEMP:

LOAD ID,

DATE,

Month(DATE) as MONTH,

Month(DATE)&'-'&Year(DATE) as MONTH_YEAR,

PAID,

WAS_INVOICED,

INVOICE_ID

FROM

[D:\Temp\community_q.xls]

(biff, embedded labels, table is Sheet1\$);

Result:

LOAD *,

If(INVOICE_ID=Previous(INVOICE_ID)

and Outstanding <=0

and peek(Outstanding) >0, 1,0) as IsFullyPaid;

LOAD

*,

IF(INVOICE_ID<>Previous(INVOICE_ID),

rangesum(WAS_INVOICED,-PAID),

rangesum(Peek(Outstanding),-PAID)) as Outstanding

RESIDENT

TEMP

ORDER BY

ID,

INVOICE_ID,

DATE

;

DROP TABLE Temp;

You can then count the fully paid invoices per month with count({<IsFullyPaid={1}>}distinct INVOICE_ID)

• Re: Counting problem with rates

Thanks.

I wanted to solve with the SET analysis but I was almost convinced that I had to fix the data model. This is good solution, but I will create new extraction from database.

• Re: Counting problem with rates

I think this is a feature you should add to your data model. Group your invoices, sum the paid amount and if the paid amount = invoiced amount, add a flag Closed = 1 (or the inverse 'Open = 0') to the last line (most recent date).

Then use set analysis to only include Closed invoices in your reporting.

Peter

• Re: Counting problem with rates

Something like:

CheckOpenorClosed:

LOAD INVOICE_ID,

Max(DATE) AS DATE,

IF (sum(PAID) >= sum(WAS_INVOICED), 1, 0) AS ClosedFlag

RESIDENT Payments

GROUP BY INVOCIE_ID;

LEFT JOIN (Payments)

LOAD INVOICE_ID, DATE, ClosedFlag

RESIDENT CheckOpenorClosed

WHERE ClosedFlag = 1;

DROP Table CheckOpenorClosed;

• Re: Counting problem with rates

Thanks. This was the last option

• Re: Counting problem with rates

May be:

=Count( {\$<PAID = {'>0'}, WAS_INVOICED={0}  >}DISTINCT INVOICE_ID )

• Re: Counting problem with rates

Hi David ,

Please use Advance Set Analysis

Count( {\$<INVOICE_ID={"=(Sum(WAS_INVOICED)-Sum(PAID) >0 "}  >}DISTINCT INVOICE_ID)

I hope it help to you