I am trying to calculate Total GP for lost customers based on the selected date.
The definition for Lost customer is based on the date whichever is selected, no transaction for the last 3 months, then these customers are lost customers, then we sum total GP for these customer at the same period last year.
For example user select 2014.12
if no transaction from 2014.9 - 2014.12, then we sum(GP) for these customers for the period of 2013.12
I defined 4 variables:
vDateTo = Date
vDateFrom = AddMonths(vDateTo,-3)
vDateLYTo = max(AddYears(Date,-1))
vDateLYFrom = =min(AddYears(Date,-1))
=sum(If(Date <= vDateLYTo and MC_Date >= vDateLYFrom and isnull(sum({<Date=>}If(Date <= vDateTo and Date >= vDateFrom , GP))), GP))
But the script doesn't give me any result, any thoughts?
Set analysis does not work in the script, only in expressions used in the front end. If you use the expression you posted in the front end then it won't work because you're nested sum functions without using the aggr function. There may be other things wrong too. It would help if you could post a document that demonstrates the problem.
