To Find Forcast for next n Months in OBIEE

Today I tried to get average forecast for next 6 months without datetime column.

I have table which has following structure.

I want average forecast of 6 months. i.e. Addition of forecast of 6 months including current.  So for JAN forecast should be 35, for FEB it should be 43.3 and so on. Problem is I cannot sort the table as I don't have any id or primary key column.

Using CASE statement in functions while creating report and using month column, mark each month by its equivalent number. 1 for JAN, 2 for FEB etc.

Sample case statement which I used.


SELECT FORCAST."YEAR" saw_0, CASE FORCAST.MONTH WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 WHEN 'APR' THEN 4 WHEN 'MAY' THEN 5 WHEN 'JUN' THEN 6 WHEN 'JUL' THEN 7 WHEN 'AUG' THEN 8 WHEN 'SEP' THEN 9 WHEN 'OCT' THEN 10 WHEN 'NOV' THEN 11 WHEN 'DEC' THEN 12 ELSE 0 END saw_1, FORCAST."MONTH" saw_2, FORCAST.FORCAST saw_3, MAVG (FORCAST.FORCAST, 6) saw_4 FROM SCOTT ORDER BY saw_0 DESC, saw_1 DESC
Result will be in reverse order

In order to get result in desired way, we will use this as sub query in new report and sort in ascending order and we can get expected output.

SELECT saw_0 saw_0, saw_1 saw_1, saw_2 saw_2, saw_3 saw_3, saw_4 saw_4 FROM (SELECT FORCAST."YEAR" saw_0, CASE FORCAST.MONTH WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 WHEN 'APR' THEN 4 WHEN 'MAY' THEN 5 WHEN 'JUN' THEN 6 WHEN 'JUL' THEN 7 WHEN 'AUG' THEN 8 WHEN 'SEP' THEN 9 WHEN 'OCT' THEN 10 WHEN 'NOV' THEN 11 WHEN 'DEC' THEN 12 ELSE 0 END saw_1, FORCAST."MONTH" saw_2, FORCAST.FORCAST saw_3, MAVG (FORCAST.FORCAST, 6) saw_4 FROM SCOTT ORDER BY saw_0 DESC, saw_1 DESC) sout ORDER BY saw_0, saw_1

Using above query we get the expected output. Rename saw_# with corresponding name and we are done.