Calculation based on dynamic range

[#1]

Hello All,

I am trying to write a report to display the amount of spend by a customer in the 12 month period previous to their last paid invoice. The date of the last paid invoice will obviously be different for every customer, so I was hoping to use an if statement something along the lines of this:

sum(if(date(InvoiceDate,'YYYYMMDD')>
date(date#(text(max(InvoiceDate)),'YYYYMMDD')-366,'YYYYMMDD')
,InvoiceAmount,0))

I can get Qlikview to calculate the last invoice date per customer easily enough but when I try to use that value in an expression it give me a null answer. Has anyone achieved this sort of calculation before?

Many thanks,

N

[#2]

MIght just have cracked it myself, actually.

I thought that maybe I needed to use aggregation within the calculation to allow Qlikview to get the correct max date per customer. I came up with this:

sum(

if(date#(Text(InvoiceDate),'YYYYMMDD')<=date#(aggr(nodistinct maxstring(text(InvoiceDate)),customer),'YYYYMMDD')-366,

Invoiceamount,

0)

)