topic Re: Conditional Sum in QlikView

Conditional Sum

bruno_m_santos — Thu, 23 Aug 2012 14:31:01 GMT

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

Re: Conditional Sum

jonathandienst — Thu, 23 Aug 2012 14:51:15 GMT

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

Re: Conditional Sum

bruno_m_santos — Thu, 23 Aug 2012 15:16:03 GMT

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

Re: Conditional Sum

jonathandienst — Fri, 24 Aug 2012 08:34:58 GMT

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

Re: Conditional Sum

bruno_m_santos — Fri, 24 Aug 2012 15:41:12 GMT

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 ))