topic Set Analysis - Error comparaison two fields in QlikView

Set Analysis - Error comparaison two fields

Tue, 27 Oct 2015 14:21:57 GMT

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

Re: Set Analysis - Error comparaison two fields

swuehl — Tue, 27 Oct 2015 17:39:33 GMT

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)

Re: Set Analysis - Error comparaison two fields

Wed, 04 Nov 2015 10:04:06 GMT

Thanks a lot Swuehl, very helpfull !