Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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 ))