sum() with many filter with which function?

[#1]

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 ?

[alexandros17]

Try this and let me know

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

[#3]

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

-> I get a crash of the project..

[alexandros17]

could you sent it to me?

[#5]

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

[tresesco]

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

[MayilVahanan]

Hi

Script:

ODBC CONNECT TO Navision;

TT:

Load *;

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)

[#8]

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 ?

[#9]

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