Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)