Sum (Total... with embedded IF?

[#1]

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.

[#2]

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

[#3]

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

[#4]

dimension salesperson

expression sum(total sales) 

doesn't work for you ?

[#5]

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

[#6]

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

[#7]

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?

[#8]

Chris,

I have checked and here is the expression:

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

that should work.

[#9 · Accepted solution]

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

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

[anbu1984]

Dim: Salesperson

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