topic Re: Counting problem with rates in QlikView

Counting problem with rates

davidg_curs — Fri, 26 Feb 2016 09:07:33 GMT

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

davidg_curs — Fri, 26 Feb 2016 11:17:09 GMT

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

Re: Counting problem with rates

Gysbert_Wassenaar — Fri, 26 Feb 2016 11:20:06 GMT

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)

Re: Counting problem with rates

Peter_Cammaert — Fri, 26 Feb 2016 11:23:20 GMT

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

Peter_Cammaert — Fri, 26 Feb 2016 11:28:35 GMT

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

tresB — Fri, 26 Feb 2016 11:29:58 GMT

May be:

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

Re: Counting problem with rates

perumal_41 — Fri, 26 Feb 2016 12:37:48 GMT

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

Re: Counting problem with rates

davidg_curs — Fri, 26 Feb 2016 14:16:29 GMT

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

davidg_curs — Fri, 26 Feb 2016 14:17:29 GMT

Thanks. This was the last option