LAST year sale VS This year sale- how to create objects

building semantic layer
1

Hi
I have a fact table f_sale with measure ‘sale_qty’ and FK ‘calendar_dwk’
Dimension calendar have column calendar_dwk,curr_date,curr_year,Fiscal_year,Fiscal_qtr.
I want to create object LAST_YEAR_SALE_QTY at universe level.
requirment is to drag all three object in a report to visualize differance between both year sale.
Any idea how to creat object which will show last year sale.
regards

GBS74 (BOB member since 2007-01-23)

2

Try something along the lines of:

SUM
(
   CASE WHEN FISCAL_YEAR =2008 THEN FactTable.Measure
   END
)

Note:
You might add an additional column to your calendar table: FLAG_Is_Previous_Fiscal_Year (0/1), you could then define your measure as:

SUM
(
   CASE WHEN FLAG_Is_Previous_Fiscal_Year = 1 THEN FactTable.Measure
   END
)

This flag would have to be populated and updated via your ETL process.

Andreas :de: (BOB member since 2002-06-20)

3

I think I have not express my requirement properly, let me explian again :-

dim_cal table having column cal_dwk/date/F_year
f_sale table having column cal_dwk/sale
where f_sale/cal_dwk is Forign key to calendar table.

Object in universe are:-
cal dwk
F year
Sale

I have already created following object in universe

Prev F year -(to_number(substr(( F_YEAR ),1,4))-1)||’/’||substr(( F_YEAR ),3,2) )

now I want to creat another object

Previous_year_sale

so that I can drag following object in report F_year / Sale / Previous_year_sale. Could you please tell me how to create object
Previous_year_sale.

GBS74 (BOB member since 2007-01-23)