23 Replies Latest reply: Jul 29, 2016 3:46 AM by Vineeth Pujari

Sum if Sum - Condition Expression help

Hello,

Not sure if this is possible, but with the data I am working with I am stuck.

I have these fields:

Billable Hours

Network Days Flag

Employee

I am trying to perform a calculation if the...

(sum of billable hours) - (sum of Network Days Flag * 8) >= 20, then sum the list of employees.

My current expression(that doesn't work):

Sum(if(Sum([Billable Hours])-Sum([Network Days Flag])*8>=20)[Employee])

• Re: Sum if Sum - Condition Expression help

Try this

if( Sum([Billable Hours]-(Sum([Network Days Flag])*8)>=20, count( distinct[Employee]))

• Re: Sum if Sum - Condition Expression help

This has an expression error?

• Re: Sum if Sum - Condition Expression help

if( Sum([Billable Hours])-(Sum([Network Days Flag])*8)>=20, count( distinct [Employee]))

• Re: Sum if Sum - Condition Expression help

try this...i think do you need count Employee, no????

sum({<[Employee]={"\$(=Sum([Billable Hours])-Sum([Network Days Flag])*8>=20)"}>} [Employee])

• Re: Sum if Sum - Condition Expression help

I am only trying to show employees if they meet the condition of 20 hours or more.  Employee are names, not numeric.  Thanks

• Re: Sum if Sum - Condition Expression help

Assuming Employee is a numeric field!

Sum(

if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8)>=20,[Employee])

)

• Re: Sum if Sum - Condition Expression help

Employee is not numeric, what could I do to solve this?  My issue is trying to chart (in some way) only a list of employees if they are 20 hours or more.  I have been trying to add filters, or an expression in several charts trying to accomplish this.  THANKS

• Re: Sum if Sum - Condition Expression help

Rodrigo's Solution should work for you

or else try

Sum(

if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8)>=20,1)

)

or

SUM(AGGR(Sum(

if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8)>=20,1)

),[Employee]))

• Re: Sum if Sum - Condition Expression help

So, if I have a pivot table or bar chart for example. how can I apply this?  Is their a way to make this a filter or dimension?  Right now, this only work as a measure.  So all of my employees are being charted.  I would only like to see the specific employees that are over the condition.  Thanks

• Re: Sum if Sum - Condition Expression help

AGGR(

if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8)>=20,[Employee])

,[Employee])

For Calculated Dimension

• Re: Sum if Sum - Condition Expression help

Thank you all so much.  This is the one that finally gave me my results.  I have tested the data and finally return true!

• Re: Sum if Sum - Condition Expression help

Vineeth,

The below code is working great.  Thanks again.  I was just wondering, for a calculated dimension is it possible to use a "OR" in the condition?

So can we take:

AGGR(if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8) < 0,[Employee]),[Employee])

and somehow make a calculation of

AGGR(if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8) < 0 "||" employee <= -20,[Employee]),[Employee])

I have been finally understanding the use of aggr, but can't find any documentation on this.

• Re: Sum if Sum - Condition Expression help

Change as below

AGGR(if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8) < 0 OR employee <= -20,[Employee]),[Employee])

Is employee a numeric field? You are compating it with -20

• Re: Sum if Sum - Condition Expression help

What I was trying to at was if the:

AGGR(if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8) < 0

OR

AGGR(if( Sum([Billable Hours]) - (Sum([Network Days Flag])*8) <= -20

would this work in any way?

• Re: Sum if Sum - Condition Expression help

perhaps the "AND" would be correct, as I am trying to limit the calculations between 0 and -20?

• Re: Sum if Sum - Condition Expression help

Perhaps something like this?

AGGR(if(sum([Billable Hours]) - (Sum([Network Days Flag])*8) < 0 AND AGGR(if(sum([Billable Hours]) - (sum([Network Days Flag]) * 8) <= -20)),[Employee]),[Employee])

• Re: Sum if Sum - Condition Expression help

to get values between 0 to -20 your comparison should be

<0  AND >=-20  (-10 is greater than -20, -30 is less than -20)

• Re: Sum if Sum - Condition Expression help

It is to work. he's checking account for the size , if the sum more than 20 for the employee he says. example help QlikView

sum( {\$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales )

• Re: Sum if Sum - Condition Expression help

Thank you Rodrigo, I perhaps wasn't specific enough.  I tired my best to explain.  Anyways cheers!

• Re: Sum if Sum - Condition Expression help

count(distimc {<[Employee]={"\$(=Sum([Billable Hours])-Sum([Network Days Flag])*8>=20)"}>} [Employee])

• Re: Sum if Sum - Condition Expression help

I feel like this should work, but I can only use it as a measure.  Is there any way something like this could work as a dimension?  If I use Employee as a dimension, then use this as the measure, it does not output correctly when charting.  Thank you for your help, just can't get the results I need.