Using Count on Dimension 1, need sum of above for Dimension 0

[#1]

I have a table that is displayed below. I am calculating each weeks overtime hours and std hours

Std hours formula:

40*count(empid)

OT formula:

if(sum(laborhrs)-(40*count(empid))<0 , 0 , sum(laborhrs)-(40*count(empid)))

emp count column:

if(Dimensionality()=0, count(all empid),count(empid))

My issue that I am running into is the total. I need to see the sum of the values. the issue is that the portion count(empid) is returning the wrong number (Should show 446).

How do I tell this to take the sum of the above for the total?

week                                        std                                 OT                           emp count

8/29/2011	3280.00	0.00	82
9/5/2011	3440.00	0.00	86
9/12/2011	3440.00	760.74	86
9/19/2011	3840.00	960.90	96
9/26/2011	3840.00	94.58	96
Total	4120.00	0.00	261

[its_anandrjs · Accepted solution]

Hi,

Can you try the Sum values not by Expression Total in place of that can you try Sum of Rows in the properties of chart

You have to select Emp count expression and select Sum of Rows option and Apply setting and check it.

Rgds

Anand

[#3]

Thank you, That worked.

Just curious, what would be the solution if I was using a Pivot Table? The sum of Rows option is not available there.

[swuehl]

Marc,

search for "sum of rows in pivot tables" using advanced aggregation in the Help file.

I think your expression could look like:

sum( aggr( count(empid), week))

Hope this helps,

Stefan