topic Scripting problem. in QlikView

Scripting problem.

Tue, 02 Oct 2012 17:58:48 GMT

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)

Re: Scripting problem.

deepakk — Tue, 02 Oct 2012 18:03:42 GMT

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