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
Quote from: jai1970 on 17 Jul 2014 04:00:47 AM
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.