Topic
  • 9 replies
  • Latest Post - ‏2012-10-09T14:21:38Z by DanielWagemann
LinhLe
LinhLe
6 Posts

Pinned topic AVERAGE of measure based on number of days in Tree View (prompt Page)

‏2012-10-05T08:26:31Z |
Dear all,

I'm facing an issue in Report Studio (Cognos BI 10.1). I want to make an Average item:
Avg = Total(Revenue) / number of days chosen in tree view (at Prompt page) (for Products dimension)

For example, I choose a month Sep, 2012. It must be:
Ave = Total (measures in Sep, 2012) / 30 days
The problem is If you have an Dimension (ex: Products) that don't have Revenue at all days in Sep, 2012 (ex: 28 days), so the number of days return only 28 days. I always want to divide into 30 days in Sep, 2012.

Give me a suggestion, plz.
Thanks so much.
Updated on 2012-10-09T14:21:38Z at 2012-10-09T14:21:38Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-05T13:26:43Z  
    You did not post how you are getting the count for the number of days. I am assuming it would be something like:
    
    count([Revenue] within set [Days])
    

    Where days could be a set, level, tuple etc.

    You could try the following to see if it makes a difference.
    
    count(1 within set [Days])
    
  • LinhLe
    LinhLe
    6 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-08T07:58:17Z  
    You did not post how you are getting the count for the number of days. I am assuming it would be something like:
    <pre class="jive-pre"> count([Revenue] within set [Days]) </pre>
    Where days could be a set, level, tuple etc.

    You could try the following to see if it makes a difference.
    <pre class="jive-pre"> count(1 within set [Days]) </pre>
    Sorry for response late.

    I've just try to use "count(1 within set Days)", but the result is same.
  • LinhLe
    LinhLe
    6 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-08T08:02:57Z  
    But, I've already try to View Tabular Data in the Qry, number of days returns exactly 30 days.
    Do you think the problem can resolve in Report Studio?
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-08T19:16:26Z  
    • LinhLe
    • ‏2012-10-08T07:58:17Z
    Sorry for response late.

    I've just try to use "count(1 within set Days)", but the result is same.
    Could you post your report specification and identify the object in the report spec you are having problems with. I think it would be easier to take a look rather than guessing where this is going wrong.
  • LinhLe
    LinhLe
    6 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-09T09:35:52Z  
    Could you post your report specification and identify the object in the report spec you are having problems with. I think it would be easier to take a look rather than guessing where this is going wrong.
    I've posted the Report specification. I used my package. Let see the attached file.
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-09T11:54:13Z  
    • LinhLe
    • ‏2012-10-09T09:35:52Z
    I've posted the Report specification. I used my package. Let see the attached file.
    I had a quick look. You have inflow set as the default measure of the crosstab. Please set the default measure of the crosstab to Default and see if it changes your Num column.
  • LinhLe
    LinhLe
    6 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-09T13:05:18Z  
    I had a quick look. You have inflow set as the default measure of the crosstab. Please set the default measure of the crosstab to Default and see if it changes your Num column.
    Thanks to your response.

    If I change the default measure of the crosstab to Num column. All of the column in Crosstab (ex: INFLOW) will be shown in 0 value. What is next step?
  • LinhLe
    LinhLe
    6 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-09T13:06:40Z  
    • LinhLe
    • ‏2012-10-09T13:05:18Z
    Thanks to your response.

    If I change the default measure of the crosstab to Num column. All of the column in Crosstab (ex: INFLOW) will be shown in 0 value. What is next step?
    Sorry, All of the column in Crosstab (ex: INFLOW) will be shown in 30 value (Num value).
  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: AVERAGE of measure based on number of days in Tree View (prompt Page)

    ‏2012-10-09T14:21:38Z  
    • LinhLe
    • ‏2012-10-09T13:06:40Z
    Sorry, All of the column in Crosstab (ex: INFLOW) will be shown in 30 value (Num value).
    Since 30 seems to be correct number, it supports that the count 1 caclulation is being thrown off by the measure context assigned to the data container.

    If you assign a default measure only days that have that measure will be counted. If you remove the default measure all days will be counted but you will have to manually project the measures where you need them.

    For example a crosstab that has Product Line on the Rows, and Current Month on the columns with a default measure of Revenue can be reworked to have Product Line on the Rows, no default measure defined and a column calculation of tuple(CurrentMonth,Revenue).