Forum Stats

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


Calculate last day sum.

AM_1 Member Posts: 61 Blue Ribbon

I'm working on OBIEE over Essbase.

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

- 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,


  • Dimitris
    Dimitris Member Posts: 454
    if i understund your 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....

  • 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,

    Edited by: Kranthi.K on Jul 30, 2009 4:46 AM
  • AM_1
    AM_1 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 :


    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,

    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....

This discussion has been closed.