Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jananireddy
Partner - Creator
Partner - Creator

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?

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

9 Replies
hic
Former Employee
Former Employee

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

sunny_talwar

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)

hic
Former Employee
Former Employee

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

sunny_talwar

Awesome, thanks for the explanation hic

jananireddy
Partner - Creator
Partner - Creator
Author

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

alextimofeyev
Partner - Creator II
Partner - Creator II

jananireddy,

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.

jananireddy
Partner - Creator
Partner - Creator
Author

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

alextimofeyev
Partner - Creator II
Partner - Creator II

Janani,

sunindia‌ 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

hic
Former Employee
Former Employee

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