Set Analysis - Error comparaison two fields

[#1]

Hi all,

Here's an expression which doesn't work :

COUNT({$<clientYearCreationDate= {"$(=YEAR(localtime()))"},  clientMonthCreationDate= {"=(clientMonthCreationDate >= transactionMonthCreationDate - 2)"}>} distinct transactionId)

I think that the problem comes from this part :

clientMonthCreationDate= {"=(clientMonthCreationDate >= transactionMonthCreationDate - 2)"}

Could you please hep me ?

Best regards,

Simon

[swuehl · Accepted solution]

You probably want to select each record where (clientMonthCreationDate >= transactionMonthCreationDate - 2), but you are using it as a search expression on field clientMonthCreationDate. When there are multiple transactionMonthCreationDate per clientMonthCreationDate (which I assume), this logic probably won't return an unambiguous result, right?

If you want to use set analysis, you would need to apply the set modifier for a field that is unique per combination of clientMonthCreationDate and transactionMonthCreationDate, e.g. a primary key for that table.

COUNT({$<clientYearCreationDate= {"$(=YEAR(localtime()))"},  primaryKey = {"=(clientMonthCreationDate >= transactionMonthCreationDate - 2)"}>} distinct transactionId)

Or use an if() statement:

COUNT({$<clientYearCreationDate= {"$(=YEAR(localtime()))"} >} distinct If( (clientMonthCreationDate >= transactionMonthCreationDate - 2), transactionId))

Or try to flag the records that pass your condition in the script, then use set analysis for that flag

COUNT({$<clientYearCreationDate= {"$(=YEAR(localtime()))"},  flag = {1}>} distinct transactionId)

[#3]

Thanks a lot Swuehl, very helpfull !