topic Re: sum() with many filter with which function? in QlikView

sum() with many filter with which function?

Tue, 24 Jun 2014 12:21:40 GMT

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?

alexandros17 — Tue, 24 Jun 2014 12:24:22 GMT

Try this and let me know

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

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

Tue, 24 Jun 2014 12:35:16 GMT

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?

alexandros17 — Tue, 24 Jun 2014 12:46:27 GMT

could you sent it to me?

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

Tue, 24 Jun 2014 12:59:44 GMT

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

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

tresB — Tue, 24 Jun 2014 13:02:24 GMT

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?

MayilVahanan — Tue, 24 Jun 2014 13:05:06 GMT

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)

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

Tue, 24 Jun 2014 13:09:42 GMT

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?

Tue, 24 Jun 2014 13:17:49 GMT

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