Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.8K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Calculate last day sum.

AM_1
Member Posts: 61 Blue Ribbon
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
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
Answers
-
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/
/////////////////////////////////////// -
Hi alex,
From your query i understood isselect 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 -
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 -
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/
///////////////////////////////////////
This discussion has been closed.