3 Replies Latest reply on Mar 21, 2014 1:21 AM by vishwanath Pendyala

    loop LOOKUP until first non null

    Emil Schweiger

      Dear Forum,

       

           I have a slight problem which with maybe you can help.

      I'm doing a comparison calculation on consumer satisfaction levels vs. the previous month for several factors. In most cases a nice application of LOOKUP and ZN would work however I have cases where the previous month has no data for that factor and then I would need the calculation to continue the LOOKUP(ZN...) until a month is reached where the expression is non null.

      I've tried playing around with IFNULL, etc. but I just could not figure it out so any help would be appreciated.

       

      For reference at the present the calculated field is:

       

      (ZN(AVG([Q2_Waiting time_phone_time])) - LOOKUP(ZN(AVG([Q2_Waiting time_phone_time])), -[Count])) / ABS(LOOKUP(ZN(AVG([Q2_Waiting time_phone_time])), -[Count]))

       

        where [Count] is a countup parameter where I can set to how many entries ago it should compare it.

       

      (Tableau ver. 7)

       

      thanks in advance


      Emil