Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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 ?

8 Replies

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

Try this and let me know

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

Not applicable

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?

Not applicable

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

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

MVP
MVP

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: sum() with many filter with which function?

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)

Not applicable

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 ?

Not applicable

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

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