Conditional Sum

[bruno_m_santos]

Dear all,

I'm facing the follwing problem.

I've a pivot table with two dimensions:

WorkType, [Division-> Depart-Employee]

Worktype as column

Group [Division-> Depart-Employee] as lines

The metric is hours of work.

I need to create an expression that sums some types of worktype and that its results appear in all columns.

I built the following expression, but the value (although well calculated) only appears for the first condition worktype instead of repeating in all columns.

sum
( aggr( sum(if(WorkType='B' or 
WorkType='C' or 
WorkType='D', Hours)),[Division->Depart->Employee])) 

How can I solve this requirement ?

Help please

Thanks in advance

Bruno

[jonathandienst]

Hi

If you use Aggr() inside a chart or table, then you must include all the table dimensions in the Aggr(). In your case, Add WorkType...

sum ( aggr( sum(if(WorkType='B' or WorkType='C' 
or WorkType='D', Hours)),[Division->Depart->Employee], Worktype)) 

Hope that helps

Jonathan

[bruno_m_santos]

Hi Jonathan,

Thanks your help, but does not have the expected behavior...

The need is that the sum of the columns (B, C and D) are visible in all columns.

Is just adding the corresponding value from column.

Thanks

Bruno

[jonathandienst · Accepted solution]

Bruno

I assume that Cond Sum is the expression with the problem?

If you need data from B, C and D to appear in each case, then try this:

sum ( aggr( sum(Total <WorkType> if(WorkType='B' or WorkType='C' 
or WorkType='D', Hours)),[Division->Depart->Employee], Worktype)) 

Regards

Jonathan

[bruno_m_santos]

Hi Jonathan,

I had to change a little because I wanted to sum lines and not columns, but it worked perfectly.

Thanks a lot

Bruno

sum(Aggr( sum(TOTAL <[Division->Depart->Employee]> if(WorkType='B' or WorkType='C' or WorkType='D'
, Hours,0)),[Division->Depart->Employee],WorkType ))