Skip to Content

Display value at end of period

I am designing a universe which returns sales and assets, and have a time hierarchy like Year->Quarter->Month->Date. If I include the Assets measure in a report, the default behavior is to summarize, which is fine for all the other measures.

What I need to happen is that the report would display the last value in the period for Assets.

Assets and Sales are in separate tables, but I could combine them. I'm thinking that being in separate tables might be a benefit in this situation.

Any thoughts?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    May 21, 2009 at 08:13 PM

    Hi Kevin,

    You could do this using use the underlying Dbs analyitcal functions

    Something like

    MAX(Asset) KEEP (DENSE_RANK LAST ORDER BY date) OVER (PARTITION BY period) "Highest"

    Regards

    Alan

    Add comment
    10|10000 characters needed characters exceeded

    • Actually, that wouldn't help anyway. If I were to use a database function to return the max value in each period, and then add a summary field, I would still get a summary of the assets over the period, which is almost always the incorrect way to look at the data since asset values are already aggregate.

      I need to find a way to make the report or universe time-period aware, which is something that Cognos Transformer does by default, so I haven't faced the challenge before.