## Forum Stats

• 3,817,447 Users
• 2,259,334 Discussions

Discussions

# Calculate last day sum.

Member Posts: 61 Blue Ribbon
edited Jul 30, 2009 9:10AM
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')

Alex
Tagged:

• Member Posts: 454
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',......

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/
///////////////////////////////////////
• Member Posts: 1,586
edited Jul 30, 2009 7:47AM
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
• Member Posts: 61 Blue Ribbon
edited Jul 30, 2009 8:37AM
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
• Member Posts: 454

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/
///////////////////////////////////////
This discussion has been closed.