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 (Total... with embedded IF?

Hello,

I'm trying to get the total of each column by using the sum(total.... command.  However I also have an embedded If statement and it's producing zero results.

So here is the formula I'm using is this:

sum (total if (sum SALES) > 10, 1, 0)

So I thought that this would produce a total of all rows that had sales greater than $10, but I get zero results.

If I put in  just

if (sum SALES) > 10, 1, 0)

I get a result of 1 or 0 for each row (dimension SALESPERSON)

Any idea why when I add 'sum (total', that I get zero results / no sum ?

Thanks.

1 Solution

Accepted Solutions
Not applicable
Author

hahah thanks... I came up with a similar solution

sum (TOTAL if (aggr (sum (SALES), SALESPERSON) > 10, 1, 0))

View solution in original post

11 Replies
Not applicable
Author

sum (total if (sum SALES) > 10, 1, 0)  should be sum (total if (sum (SALES) > 10, 1, 0))

however that will just turned out to sum (total 1) or sum(total 0) which in turn will result in 1 or 0


is this a straight table ?  you can just write an expression like sum(sales) and put your dimensions accordingly and then check to add totals at the bottom and that will work automatically per column. if not please , explain with some sample data and the output you expect

Not applicable
Author

yes it's a straight table (eventually to be a bar chart however with just the total), but I'm trying to display the same total (sum of all rows) in each row.

So lets say only 3 of the following salespeople below had sales over $10, i would look like this

SALESPERSON        TOTAL SALES (ALL SALES PERSONS)

--------------                    --------------------------

Chris                                       3

Judy                                       3

Tracy                                      3

Kevin                                      3

Mark                                       3

Not applicable
Author

dimension salesperson

expression sum(total sales) 

doesn't work for you ?

Not applicable
Author

sum (total sales) works just fine, but I'm trying to count total sales ONLY when the sale is over $10.  So I have to have the If statement in there.....  I *thought* this would work, but it does not:

sum (total if (sum (SALES) > 10, 1, 0))

Not applicable
Author

the SUM(TOTAL ) as you explained will be similar for all the rows, I am not sure what you are trying to accomplish but here is your expression:

SUM( TOTAL IF (

                                SUM(TOTAL SALES)  > 10, 1 , 0

                             )

         )

or

SUM( TOTAL IF ( SUM(TOTAL SALES)  > 10, 1 , 0 ) )

Not applicable
Author

Your formula produces a '1' for every row.  I'm trying to get a '3' for each row, which would denote that 3 salesperson exceeded $10 in sales.

Below is both your formula (producing 1's for every row), and then my original formula, which is producing nothing.... do I need an AGGR function somehow?

Not applicable
Author

Chris,

I have checked and here is the expression:

SUM(TOTAL AGGR(IF (SUM(SALES) > 10,1,0), SALESPERSON)

that should work.

Not applicable
Author

hahah thanks... I came up with a similar solution

sum (TOTAL if (aggr (sum (SALES), SALESPERSON) > 10, 1, 0))

anbu1984
Master III
Master III

Dim: Salesperson

Expr: Count(TOTAL If(Aggr(Sum(Sales),Salesperson)>10,1))