Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

davidg_curs
New Contributor

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
Tags (1)
1 Solution

Accepted Solutions

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

(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
8 Replies
davidg_curs
New Contributor

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

(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

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;

MVP
MVP

Re: Counting problem with rates

May be:

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

perumal_41
Valued Contributor II

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

davidg_curs
New Contributor

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.

davidg_curs
New Contributor

Re: Counting problem with rates

Thanks. This was the last option

Community Browser