8 Replies Latest reply: Jun 24, 2014 9:09 AM by Tobias Meyer

# sum() with many filter with which function?

Hi,

I've many IDs with Amount like:

ID, Amount

400000, 4500

...,...

999999,4000

Now I need to sum() all the ID from: 400000 - 59999 AND 677000  AND 688000, except 494500, 494700,494800

How can I express this?

Do you have an idea ?

• ###### Re: sum() with many filter with which function?

Try this and let me know

Sum({\$ <ID={'>=400000<=59999', '>=677000<=688000'}-{'494500','494700','494800'}>} Amount)

• ###### Re: sum() with many filter with which function?

sum(\$ <ID={'>=400000<=599999','677000','688000'}-{'494500','494700','494800'}>} Amount)

-> I get a crash of the project..

• ###### Re: sum() with many filter with which function?

could you sent it to me?

• ###### Re: Re: sum() with many filter with which function?

u cant test it, because the database is local ..

• ###### Re: sum() with many filter with which function?

Set analysis can't be used in the script. You should try the expression in the front end.

• ###### Re: Re: sum() with many filter with which function?

How could the expression looks like?

Is it possible to sum() alle the values that I need, and at least subtract the sum() of the values that I want to except ?

like:

sum(amount) as sum1

sum(amount) as sum2

sum(amount) as subtractiom1

(sum1+sum2) -subtraction1

How can this be solved ?

• ###### Re: sum() with many filter with which function?

Hi

Script:

ODBC CONNECT TO Navision;

TT:

SQL SELECT Amount,

"Global Dimension 1 Code",

"Global Dimension 2 Code",

"Posting Date",

"G_L Account No_"

FROM "DBase".dbo."DB9";

Front view:

Create a chart by using "Global Dimension 1 Code" as dimension

Expression:

sum({\$ <"Global Dimension 1 Code"={'>=400000<=599999','677000','688000'}-{'494500','494700','494800'}>} Amount)

or

Load * where not match ("Global Dimension 1 Code", '494500','494700','494800);

SQL SELECT Amount,

"Global Dimension 1 Code",

"Global Dimension 2 Code",

"Posting Date",

"G_L Account No_"

FROM "DBase".dbo."DB9";

Front view:

Create a chart by using "Global Dimension 1 Code" as dimension

Expression:

sum(Amount)

• ###### Re: sum() with many filter with which function?

You can do the same at the back end and front end.