Forum Stats

  • 3,851,925 Users
  • 2,264,053 Discussions
  • 7,904,906 Comments

Discussions

Calculate last day sum.

AM_1
AM_1 Member Posts: 64 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
Tagged:

Answers

  • Dimitris
    Dimitris 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',......

    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
    Kranthi.K 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
  • AM_1
    AM_1 Member Posts: 64 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
  • Dimitris
    Dimitris Member Posts: 454
    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/
    ///////////////////////////////////////
    Dimitris
This discussion has been closed.