3 Replies Latest reply: Oct 12, 2011 4:19 PM by Stefan Wühl

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

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 0 82 9/5/2011 3440 0 86 9/12/2011 3440 760.74 86 9/19/2011 3840 960.9 96 9/26/2011 3840 94.58 96 Total 4120 0 261
• ###### Using Count on Dimension 1, need sum of above for Dimension 0

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

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

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.

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

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