Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jananireddy
Contributor

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
Employee
Employee

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

9 Replies
Employee
Employee

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

MVP
MVP

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)

Employee
Employee

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

MVP
MVP

Re: Set Analysis Expression

Awesome, thanks for the explanation hic

jananireddy
Contributor

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

alextimofeyev
Contributor II

Re: Set Analysis Expression

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
Contributor

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?

alextimofeyev
Contributor II

Re: Set Analysis Expression

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

Employee
Employee

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