Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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)
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
Awesome, thanks for the explanation hic
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
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.
Thanks Alex, But I cant be creating a field for the date difference. In there any other way to be re writing this query?
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
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