Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.]
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.
There are at least two ways. You can use the above() function in charts or the previous function in the load script. I've used both in the attached example so you can see how they work.
Thank you so much, Gysbert Wassenaar!!!