Calculate last day sum.

AM_1

Hi,

I'm working on OBIEE over Essbase.

Dimensions:
DATE DIM: - Year/Month/Date
PRODUCT DIM: Product Group / Product

Measures:
- Product quantity

Products are not produced every day.

How can I calculate for a product group, sum of product quantity for all the products in the group, for the last day when any of the products within this product group was produced ?


In SQL, it would look like:

select sum(product_qty) from FACT where date in (select max(date) from FACT where product_group = 'MY_GROUP')


Thanks in advance,
Alex

Dimitris

hi,
if i understund your question...it has nothing to do with programming but with taking a decision .

You have to choose....Meaning,

If for one day you have '0' then what???show null,o,'Sorry no sails',......

this can be done at your column by adding
case when "Column"."Product quantity" = 0 then 'SoRrY nothing here' else "Column"."Product quantity" end

cast the 'Sorry' message not to have problems or add a numeric value...


hope i helped....

///////////////////////////////////////
http://greekoraclebi.blogspot.com/
///////////////////////////////////////

Kranthi.K

Hi alex,

From your query i understood is

select sum(product_qty) from FACT where date in (select max(date) from FACT where >product_group 'MY_GROUP')

You can write it this way to get sum for that product

select sum(product_qty),max(date) from FACT where product_group = 'MY_GROUP'
and date <=(TO_DATE(:DATE,'YYYY/MM/DD HH24:MI:SS'))

here in the above query i gave you sud pass the last day of the date which is the parameter in this case.If you want you can use a group by product_group if your not using specific product_group,i mean hard coding it.


Hope this helps you.

Best Wishes,
Kranthi.

Edited by: Kranthi.K on Jul 30, 2009 4:46 AM

AM_1

Hi all.
Thanks for the replies.

I'd like to clarify the question:

What was the total produced quantity of a product X during the last day when it was produced

Say, the data is :

DATE PRODUCT QTY

2009/01/01 PRODUCT1 1
2009/01/01 PRODUCT2 3
2009/01/01 PRODUCT2 2
2009/01/02 PRODUCT1 4


The answer for PRODUCT1 is 4 (the last date is 2009/01/02)
The answer for PRODUCT2 is 5 (the last date is 2009/01/01)

How can I calculate this in OBIEE or in Essbase?

Best Regards,
Alex

Edited by: AM_1 on Jul 30, 2009 5:36 AM

Dimitris

now,understood your question....

You have a report....
and you want to have the sum(let;s say of quantity)of the last day that there is a value....

So, you have to use filter function in order to achieve that.

Use the previous mentioned function of retreiveing the last date AND with Values

--->
Filter("Table"."Column" using DAteColumn = (the_function) )



hope i helped....

///////////////////////////////////////
http://greekoraclebi.blogspot.com/
///////////////////////////////////////