Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 * 😎 >= 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])

Thanks in advance

22 Replies
Not applicable
Author

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

send us your sample .QVF file we can be able help you

vinieme12
Champion III
Champion III

AGGR(

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

        ,[Employee])


For Calculated Dimension

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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 )

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Thanks in advance

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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?