Expression in Set Analysis

[zekazak]

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

[#2]

Hi,

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

Regards,

Björn

[perumal_41]

Hi,

What problem .Please upload your qlikview file.

[swuehl]

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

[#5]

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

     OR

      Statement.amount <=-10000)

Malki Ely

[zekazak]

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

[swuehl]

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

[zekazak]

Hi Stefan,

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

~Sergejs