If and Lookup

[#1]

Hi,

I am new to qlikiew. I am wondering how can use If and Lookup function together.

Shoaib

[tresesco]

Could you explain your requirement ?

[#3]

Thanks , explanation as below:

App_ID          Event_Typ          Event Date

12345678      Entered               01/01/2013

12345678     Referral Event       05/01/2013  

98765432     Entered                01/01/2013   

98765432     Referral Event       05/01/2013  

98765432     Entered               07/01/2013

98765432     Referral Event      10/01/2013

So i want to calculate the average no of days of the difference between last instance of Referral event and the first instance of Entered.    

[tresesco]

Not yet very clear to me. I am just thinking aloud, you might have to use intervalmatch() of max(), min() in combination.

[#5]

I want to calculate the different between Referral event and entered date. Enetered and Referral event could be multiple times for one APp_ID. So should be the first instance of Entered and the last instance of Referral event. If I get this difference then need to use its average.

[davidrobles]

Try

load

     App_ID,

    Event_Typ               as "Start Event",

   Event Date               as "Start Date"

where Event_Typ=Entered   

load

     App_ID,

    Event_Typ               as "End Event·,

   Event Date               as "End Date"

where Event_Typ= Referral Event

From your Data

[jonathandienst]

Hi

You need something like this:

Avg(Aggr(Max({<Event_Typ={"Referred*"}>} [Event Date]) - Min({<Event_Typ={"Entered*"}>} [Event Date]), App_ID))

Jonathan

[tresesco]

May be like attached sample

Edit: Jonathan Dienst is quick! He is right.