Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crosstab average not ignoring nulls

Status
Not open for further replies.

aks12

Technical User
Nov 10, 2005
60
US
I have a simple crosstab in Report Studio that looks at a measure for months throughout the year across a few intervals. I have a calculated member at the bottom of my crosstab that creates an average for each column. My problem is that Cognos is treating nulls as 0 and including them in the average calculation and is giving the incorrect average results as seen below. For example, the average for Int1, Int2, and Int3 should be 25, 20 and 15. Does anybody know how to fix this?

Code:
[b]       Int0 Int1 Int2 Int3[/b] 
[b]Jan[/b]      10   10   10   10 
[b]Feb[/b]      20   20   20   20
[b]Mar[/b]      30   40   30     
[b]Apr[/b]      40   30
[b]May[/b]      50 
[b]Average[/b]  30   20   12    6
 
I solved this by first creating a calculation that creates a count of the months if my measure > 0. Then I created a calculation that summed the measure in each column. Then it was easy to create an average calcuation like [Count]/[Total].
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top