topic Expression in Set Analysis in QlikView

Expression in Set Analysis

zekazak — Tue, 11 Oct 2011 11:06:55 GMT

Hello,

I've stuck with set analysis formula. Basically I want to get table to dispaly the data only where Statement.Amount devided by Currency.Exchangerate is >= than 10000 or <= than - 10000.

The formula should look something like this:

=sum({$<Statement.amount={">=10000<=-10000"}>}Statement.amount)

But 'Statement.amount' needs to be devided by 'Currency.Exchangerate' first in this formula. Does anyone know how to achieve this?

Any idea will be very helpful for me. Thank you in advance!

~Sergejs

Expression in Set Analysis

Tue, 11 Oct 2011 11:11:23 GMT

Hi,

I would suggest to do this calculation in the script and to create a new field.

Regards,

Björn

Expression in Set Analysis

perumal_41 — Tue, 11 Oct 2011 11:36:27 GMT

Hi,

What problem .Please upload your qlikview file.

Expression in Set Analysis

swuehl — Tue, 11 Oct 2011 11:41:24 GMT

Why not multiply your limits (10000 and -10000) with Currency.Exchangerate in your search expression?

(well, does your search expression results in any records? I wouldn't think that >=10000 and <=-10000 is possible for any value of amount, I belive the search string uses an implicite AND operator).

This will probably only work with a fixed, single Currency.Exchangerate in the scope of the set expression.

Hm, I assume you have a Currency.Exchangerate that may change by record, right?

Then I think you can't use the set expression, you need a record based evaluation:

=sum(if( (Statement.amount / Currency.Exchangerate) >=10000 or (Statement.amount / Currency.Exchangerate) <=-10000, Statement.amount / Currency.Exchangerate)

And I think Björn is right, performing the currency calculation in the script is probably better.

Regards,

Stefan

Expression in Set Analysis

Tue, 11 Oct 2011 12:59:16 GMT

Hi,

you can set your Set Analysis like:

sum({$<Statement.amount={">=10000"}>+<Statement.amount={"<=-10000"}>}Statement.amount)

If can use + operator like OR logical.

This will be behaviour of your expression:

WHERE

(Statement.amount >=10000

Statement.amount <=-10000)

Malki Ely

Expression in Set Analysis

zekazak — Tue, 11 Oct 2011 14:23:11 GMT

Hello guys,

Thank you all for quick responses and sorry for poor explanation of the problem from my side.

So this formula '=sum({$<Statement.amount={">=10000<=-10000"}>}Statement.amount)' works perfectly for me.

We use to use this formula in our reports, but recently I realized that Statement.amount field value needs to be devided by Currency.exchangerate. I just wanted to modify my set analysis formula rather than modify the load script.

And I would like to create the formula something like this:

=sum({$<Statement.amount/Currency.exchangerate={">=10000<=-10000"}>}Statement.amount/Currency.exchangerate).

Thanks,

~Sergejs

Expression in Set Analysis

swuehl — Tue, 11 Oct 2011 15:07:37 GMT

Hi Sergejs,

I believe you can't use an expression like Statement.amount/Currency.exchangerate on the left side of a set analyis expression assignment. You must state a field here - you could think of field selection, so what field do you wanna select in giving an expression like amount / exchangerate?

This is not like the left side of a comparison, it's more like an assignment.

Have you tried the sum with the if clause I posted above?

Regards,

Stefan

Expression in Set Analysis

zekazak — Mon, 30 Jan 2012 17:24:44 GMT

Hi Stefan,

Thanks for your help. Your idea helped me a lot.

~Sergejs