Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try this:
TEMP:
LOAD ID,
DATE,
Month(DATE) as MONTH,
Month(DATE)&'-'&Year(DATE) as MONTH_YEAR,
PAID,
WAS_INVOICED,
INVOICE_ID
FROM
(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)
hic Would you be so kind and take a look at this problem and tell me if is possible?
Try this:
TEMP:
LOAD ID,
DATE,
Month(DATE) as MONTH,
Month(DATE)&'-'&Year(DATE) as MONTH_YEAR,
PAID,
WAS_INVOICED,
INVOICE_ID
FROM
(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)
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
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;
May be:
=Count( {$<PAID = {'>0'}, WAS_INVOICED={0} >}DISTINCT INVOICE_ID )
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
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.
Thanks. This was the last option