Topic
• 9 replies
• Latest Post - ‏2012-10-09T14:21:38Z by DanielWagemann
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
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
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
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
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
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.

Attachments

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

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

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