2 Replies Latest reply: Dec 5, 2012 1:39 AM by qlikview11v RSS

    Is there any similar function in QlikView for Oracle BI function AGO

      Hi ALL,

       

      I have to convert the following expression in Oracle BI into QlikView:

       

      ---> sum(fo.sum_1) / nullif( Ago(sum(fo.sum_1), [Level Year], 1) , 0) * 100

       

      [

       

      AGO,MAGO,YAGO,QAGO

      AGO(<measure>, <level>, <shift needed>)

      ]

      [In Oracle, the SUM returns the sum of values of expr.]
      [In Oracle, the NULLIF function compares expr1 and expr2.
      If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.]

       

      Snap5.jpg

       

      As you see above, QAGO has shifted the values by 1 quarter,

      and AGO has shifted the values by 1 year -  Ago(sum(fo.sum_1), [Level Year], 1)

       

       

       

       


      I am thinking I might have to do something in the script or am I wrong?

       

      with t_ago as

      (

      select sum(fo.sum_1) as ago_sum_1,C.col1

      from t_dim c

      join t_fact fo on FO.col_key = C.col_key

      join t_dim_date d on D.DATEKEY=FO.DATEKEY

      where D.CYEARNAME = '2005'

      group by C.col1

      order by C.col1

      )

      SELECT col1

           , col2

           , col3

           , col4

           , ROUND(col / nullif( a.ago_sum_1 , 0) * 100,1) || '%' as "col5, %"

      FROM

          (

          select C.col1

               , sum(FO.sum_1) as col2

               , sum(FO.sum_2) as col3

               , sum(fo.sum_3) as col4

               , sum(fo.sum_1) as col

          from t_dim c

          join t_fact fo on FO.col_key = C.col_key

          join t_dim_date d on D.DATEKEY=FO.DATEKEY

          where D.CYEARNAME = '2006'

          group by C.col1

          order by C.col1

          ) inview

      JOIN t_ago a on a.col1 = inview.col1

       

       

      Please help in creating this logic.

       

      Thanks in Advance.