9 Replies Latest reply: Mar 15, 2016 7:20 AM by Henric Cronström

# Set Analysis Expression

Hey Guys, i need help in writing a set analysis query for the below IF expression

COUNT(IF((DATE - DATE1) <= 7 and DATE >= DATE1 and CATEGORY = 'CASA', ACCOUNTID))

The query i wrote (pasted below), is not giving me the correct count of account id's

COUNT({\$< CATEGORY = {'CASA'}, ACCOUNTID= {'DATE >= DATE1'}, ACCOUNTID= {'(DATE - DATE1) <= 7'}>} ACCOUNTID)

where am i going wrong?

• ###### Re: Set Analysis Expression

The problem here is that you want a condition to be evaluated on a row-by-row basis. But Set Analysis cannot do that.

However, if you in the script define

DATE-DATE1 as DateDiff

you can define the above condition as a Set Analysis expression:

COUNT({\$<CATEGORY={'CASA'}, DateDiff={">=0<=7"}>} ACCOUNTID)

HIC

• ###### Re: Set Analysis Expression

Henric correct me if you think I am wrong, but if each account is uniquely associated with a DATE and DATE1, would then we be able to use this?

Count({\$< CATEGORY = {'CASA'}, ACCOUNTID = {"=DATE - DATE1 >= 0 and DATE - DATE1 <= 7"}>} ACCOUNTID)

• ###### Re: Set Analysis Expression

Yes that's true. Your formula will be evaluated as

... ACCOUNTID = {"=Only(DATE) - Only(DATE1) >= 0 and Only(DATE) - Only(DATE1) <= 7"} ...

and that will work fine as long as there is exactly one value of each date for an ACCOUNTID. But it breaks if you have several or none.

HIC

• ###### Re: Set Analysis Expression

Awesome, thanks for the explanation hic

• ###### Re: Set Analysis Expression

Thanks HIC. I tried your expression, but the count is not matching.

The count of account id's for the if condition is 114.4 k, but your condition is giving me 232.3 k

I have attached the .qvf file here for reference. Set analysis needs to be applied on the first two KPI's

• ###### Re: Set Analysis Expression

I think Henric meant that you should create a field called DateDiff, not a variable. I don't think you can use an expression on the left hand side of the set modifier, only field names.

• ###### Re: Set Analysis Expression

Thanks  Alex, But I cant be creating a field for the date difference. In there any other way to be re writing this query?

• ###### Re: Set Analysis Expression

Janani,

Sunny T has proposed a solution above that works if you always have those dates for each CustomerAccountId, and never have more than one such date per id (which seems to be the case).

Alex

• ###### Re: Set Analysis Expression

Why can't you create a field for the date difference? APPLICATIONDATE and DATESTART are in the same table, so this should be easy.

But if you don't want to do this, you can use Sunny's suggested formula. It should work.

HIC