Average in Cross Tab Eliminates the null values.

[jai1970]

Hi

I am having an issue while calculating Average in the Cross tab.  I got Months as Rows and Category as Columns, with a measure value. For one category i dont have values for a month, the Average in the bottom does not show the correct value. It is not considering the Total number of Month, if there is no value.
I am providing an example below.

Note: My Months are user selection from the Prompt page.

COGNOS RESULT
---------------------
Month                    Cat1         Cat2        Cat3
------------------------------------------------------------
April                                         5                3
May                          2              2                4
June                         3              1                4
-------------------------------------------------------------
Average                2.5           2.66            3.66
-------------------------------------------------------------

EXPECTED RESULT
------------------------
Month                    Cat1         Cat2        Cat3
------------------------------------------------------------
April                                         5                3
May                          2              2                4
June                         3              1                4
-------------------------------------------------------------
Average                1.6           2.66            3.66
-------------------------------------------------------------

Note:  I dont have records for April - Cat1,  so it is showing blank value. 

Tried
--------
1. Null value replaced with 0
2. Tried Sum(Cat1) / Promptcount  (Throwed error promptcount cannot be used in expression)
3. Tried creating Dataitem (Sum Cat1 / Total(Months)

Nothing worked for me,  Can anyone please help me.
Appreciate your help.

Thanks

Regards
Jaishankar B

[MFGF]

Hi

I am having an issue while calculating Average in the Cross tab.  I got Months as Rows and Category as Columns, with a measure value. For one category i dont have values for a month, the Average in the bottom does not show the correct value. It is not considering the Total number of Month, if there is no value.
I am providing an example below.

Note: My Months are user selection from the Prompt page.

COGNOS RESULT
---------------------
Month                    Cat1         Cat2        Cat3
------------------------------------------------------------
April                                         5                3
May                          2              2                4
June                         3              1                4
-------------------------------------------------------------
Average                2.5           2.66            3.66
-------------------------------------------------------------

EXPECTED RESULT
------------------------
Month                    Cat1         Cat2        Cat3
------------------------------------------------------------
April                                         5                3
May                          2              2                4
June                         3              1                4
-------------------------------------------------------------
Average                1.6           2.66            3.66
-------------------------------------------------------------

Note:  I dont have records for April - Cat1,  so it is showing blank value. 

Tried
--------
1. Null value replaced with 0
2. Tried Sum(Cat1) / Promptcount  (Throwed error promptcount cannot be used in expression)
3. Tried creating Dataitem (Sum Cat1 / Total(Months)

Nothing worked for me,  Can anyone please help me.
Appreciate your help.

Thanks

Regards
Jaishankar B

Hi,

You didn't specify if you are using a dimensional or relational package. I'm assuming the latter?

If so, try adding a query calculation to the crosstab to perform your average - use the expression:

total([your measure value]) / count([your month item] for report)

Cheers!

MF.