Grouping records by Customer then limiting the record count to 12 per customer

[#1]

I have a need to look at certain invoices for about 200k customers but only the last 12 months even though there may be more records.

• Need total billed amount
• MAX billed amount
• AVG billed amount

Again this is only for the last 12 months, some customer may have 36 months of invoices. My thought is to use "set analysis" or do some sort of limits used in script during the data load.

Sample data structure looks somting like this:

customer_number, invoice_number, invoice_date, invoice_total

Thanks for your help!

-Jason

[#2]

Hello Jason,

there is more than one possibility depending on what you want to do in addition to your billing report mentioned above.

If you do not need the data for others purposes my preference is filtering during load. When you extract from a database, use SQL (ie. where invoice_date >= '01.11.2009' (german date format). When you extract from text or excel you can use a similar expression within the load-statement of QV. Thats like LOAD ... FROM filename.xls WHERE invoicedate >= '01.11.2009'. In this case your expressions in your charts will be easyly written because you won't need much more than standard-functionality like avg(amount) and sum(amount) and so on.

If you want to do some more analysis I would load all the data and during the load creating additional calendar-fields like year, month using Year(), Month() etc. within the LOAD-Statements. See Help for details. In the charts I would then use set analysis like sum({ <invoice_date= { ">=01.11.2009" }>} amount).

Hope this helps for the moment

Roland

[#3]

Roland,

Thanks for the reply, the issue i have with using invoice date as a set date to filter from such as 01.11.2009 is that I'm looking at each customer and getting their past 12 months invoices... so i cant assume that a customer has been billed every month and their last invoice was this month or last month, they may no longer be customers. In effect I cant use all invoices from a start date instead I have to look at the last invoice per each customer going back through 12 months of invoices and limiting/including the customer's last 12 invoices only.

Thanks

-Jason

[wizardo]

hmm

how about somthing like this

temp1:

load

customer_number,

invoice_number,

invoice_date,

invoice_total

from datasource;

left join

load

customer_number,

min(invoice_date) as Min_Date

resident temp1

group by customer_number;

// you can stop here and maybe use the new field "Min_Date' in a set analysis expression

// but if you want to discard the earlier records in the script, then also add these lines:

noconcatenate

Main_Table:

load * resident Temp1 where Min_Date >= '01.11.2009';

drop table temp1;

hope that helps.

Mansyno

[#5]

Hi Jason,

sorry, I didn't catch your problem exactly from the first post. But now I would suggest to load all the data straight forward because you don't know if you need even the last incoming row for summing up. Imagine then to create of a chart with customers as a dimension and expressions like these (doing the same but one is set analysis and the other is good old if()-practice:

=sum({<InvoiceDate={ "$(= '>=' & Max(InvoiceDate)-365 )" }>} Value)

=sum( if( aggr(nodistinct Max(InvoiceDate), Customer)-365 <= InvoiceDate, Value, 0))

Note: Didn't check the syntax but it should be easy to correct it with original data.

RR