Count Based on Table

[lukegilligan]

I'm struggling to get my head around how I can count the number of Customers I have in arrears, per Portfolio, down to a single number.

For instance I have the following data set:

Portfolio	Customer.code	Customer.name	Relationship	W_InvAmount	W_RecAmount	Water Balance	In Arrears?
				9318.13	-9043.1	275.03	4
Jack	3100015	Diana Hammond	Non-Rent Ex/Trf Tenant	57.33	-57.33	0
Jack	3100020	Mechelle Yang	Non-Rent Tenant	691.9	-691.9	0
Jack	3100040	Brielle England	Non-Rent Tenant	688.5	-688.5	0
Bob	3100084	Hu Wiggins	Non-Rent Tenant	470.9	-470.9	0
Mary	3100008	Oliver Travis	Non-Rent Tenant	457.74	-457.74	0
Jane	3100042.01	Jada Best	Non-Rent Ex/Trf Tenant	167.72	-167.72	0
Peter	3100030	Minerva Oconnor	Non-Rent Tenant	275.49	-275.49	0
Peter	3100074	Xerxes Skinner	Non-Rent Tenant	510.06	-410.06	100.00	1
Fred	3100007	Adrian Serrano	Non-Rent Tenant	8.5	-8.5	0
Fred	3100010	Hu Briggs	Non-Rent Tenant	128.19	-128.19	0
Fred	3100046	Reed Monroe	Non-Rent Tenant	1868.55	-1640	228.55	1
Susan	3100014	Yasir Sears	Non-Rent Tenant	336.55	-336.55	0
Susan	3100066	Morgan Gibbs	Non-Rent Tenant	443.7	-443.7	0	1
Susan	3100069	Yuri Taylor	Non-Rent Tenant	425	-425	0
Kurt	3100042	Brody Bond	Non-Rent Tenant	756.5	-710.02	46.48	1
Kurt	3100080	Ivy Perez	Non-Rent Tenant	314.5	-314.5	0
Dave	3100049	Allistair Dyer	Non-Rent Tenant	435.2	-435.2	0
Dave	3100068	Judith Garcia	Non-Rent Tenant	1281.8	-1281.8	0

W_InvAmount is worked out by the following:

=SUM(IF(ARInvoice.orderNumber='WATER' AND ARInvoice.postingDate<=$(vReportDate),ARInvoice.rwLCGross))

W_RecAmount is worked out by the following:

=SUM(IF(ARInvoice.orderNumber='WATER' AND InvoiceApplyDetail.appliedDate<=$(vReportDate),InvoiceApplyDetail.fcAmount))

And Water Balance is worked out by adding the 2 expressions together

[W_InvAmount]+[W_RecAmount]

This then allows me to workout if [Water Balance] > 0 then put a 1 in the "In Arrears?" column.

All works perfectly whilst its in a table, however I am in the process of trying to put together KPI sheets for each Portfolio name on a separate sheet and counting how many customers they have that are in arrears as a single number.

I have moved the W_InvAmount and W_RecAmount expression formulas to a Variable and used these Variables in the Expression with the Portfolio Name as the Dimension, however it is making making my calculation based on the whole data set of the portfolio, not individual customer.

I am guessing I need to use the AGGR function somehow to accomplish this, but I am at a loss as how to how to achieve this.

Any help would be much appreciated.

[tresesco]

You are right, aggr() is something you have to look for. Try like:

Sum(Aggr(Sum(If(......)), CustomerField))

[lukegilligan]

Thanks for the reply @tresesco

So I have done the following:

COUNT(AGGR(SUM($(vCalc_WInvAmount)+$(vCalc_WRecAmount)),Customer.code))

This gives me the count per portfolio overall, but still not just at arrears level. 

Portfolio	COUNT(AGGR(SUM(9318.13+-9043.10),Customer.code))
	18
Jack	3
Bib	1
Mary	1
Jane	1
Peter	2
Fred	3
Susan	3
Kurt	2
Dave	2

This is what I am wanting it to show:

Portfolio	In Arrears?
	4
Jack	0
Bob	0
Mary	0
Jane	0
Peter	1
Fred	1
Susan	1
Kurt	1
Dave	0

[tresesco]

It seems you have to include other dimensions as well in aggr() and put a condition checking >1 in it. Could you prepare a sample app and share so that we can have a look into it and play to help?

[lukegilligan]

@tresesco I have pulled the data in to an inline table and re-created the app (there are very small discrepancies, but they won't affect the calculations).

I really appreciate you taking the time to have a look at this for me.

[tresesco · Accepted solution]

Dimension : Portfolio

Exp: Sum(Aggr(If($(vCalc_WInvAmount)+$(vCalc_WRecAmount)>0.5,1,0),Relationship,Customer.code, Portfolio))

[lukegilligan]

@tresesco Mate you are a deadest legend!! This worked perfectly.

Just so I can understand, the code does the following:

If($(vCalc_WInvAmount)+$(vCalc_WRecAmount)>0.5,1,0),Customer.code

If the balance is greater than .5 its a 1 otherwise its 0 per Customer.code

Sum(Aggr(..., Portfolio))

 This then sums the number of "1's" per Portfolio.

What does the Relationship section do in the aggr code?

Thanks again!!

[tresesco]

I saw that dimension in your table being used, so included in your calculation as well - you would know the data granularity better, if that is not impacting you can remove it.