Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davidg_curs
Contributor II
Contributor II

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.

     

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
1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
davidg_curs
Contributor II
Contributor II
Author

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

Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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;

tresesco
MVP
MVP

May be:

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

perumal_41
Partner - Specialist II
Partner - Specialist II

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

davidg_curs
Contributor II
Contributor II
Author

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.

davidg_curs
Contributor II
Contributor II
Author

Thanks. This was the last option