Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do I write an expression with conditions?
I have two date fields (referred and qualified). I want to write an experssion that says:
if the referred date field has an entry and the qualified date field has an entry, then do sum(qualified date) - sum(referred date) / count(qualified date - refererred date).
Example:
Consumer | Referred Date | Qualified Date |
---|---|---|
A | 1/1/2011 | 1/3/2011 |
B | 1/12011 | |
C | 1/1/2011 | 1/5/2011 |
So only A and C would be selected, giving : ((1/3/2011) - (1/1/2011)) / (2)
hi,
I would suggest the following method
At script level
Load Consumer ,Referred Date, Qualified Date,if( len(trim([Referred Date])) >0,'Y','N') as Ref_Ind,
if( len(trim([Qualified Date])) >0,'Y','N') as Qualifed_Ind
from Table;
At epression it would be
sum({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>}qualified date) - sum({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>}referred date) / count({<Ref_Ind={"Y"},Qualifed_Ind={"Y"}>} qualified date - refererred date)
I hope the above code helps.
Deepak