22 Replies Latest reply: Mar 8, 2018 4:06 AM by bryan dandan

# Set modifier field greater than field and variable

Hello,

I'm trying to compare a field on one part to a field and a variable on another part, inside a set modifier.

Business Rule: Cap<= (Rate + vShock)

The Cap and Rate are fields and the vShock is a variable.

I have tried the following syntaxes:

sum ({<Cap={"<=\$(=Rate + \$(vShock))"}>} Balance)

sum ({<Cap={"<=(\$(=Rate) + \$(vShock))"}>} Balance)

sum ({<Cap={"<=\$(=Rate + vShock)"}>} Balance)

[...]

I know how to use a set modifier for a variable alone, and for a field alone (dollar expansion) but I'm struggling with merging them.

Any thoughts?

Thanks

• ###### Re: Set modifier field greater than field and variable

hi

what exactly is the problem , you get unexpected results

• ###### Re: Set modifier field greater than field and variable

try this

sum ({\$<Cap={'<=\$(=Rate + \$(=vShock))'}>} Balance)

sum ({\$<Cap={'<=(\$(=Rate) + \$(=vShock))'}>} Balance)

sum ({\$<Cap={'<=\$(=Rate + \$(=vShock)'}>} Balance)

• ###### Re: Set modifier field greater than field and variable

Didnt Work. Will upload a QVW

• ###### Re: Set modifier field greater than field and variable

Sorry to ask this but how can i attach a qvw to send you?

• ###### Re: Set modifier field greater than field and variable

What dimension are you trying to evaluate this condition on?

Cap<= (Rate + vShock)

For example, let's say you have a field called Stock and you only want to find the Sum of balance where the above condition is true for a stock... then you can try this

Sum({<Stock = {"=Cap <= Rate + \$(vShock)"}>} Balance)

• ###### Re: Set modifier field greater than field and variable

The condition is on the CAP field. Will upload a QVW

• ###### Re: Set modifier field greater than field and variable

A single cap will ever have one and only one Rate?

• ###### Re: Set modifier field greater than field and variable

Sorry to ask this but how can i attach a qvw to send you?

Meanwhile, here is an example:

[

Deal, Cap, Rate, Balance

1, 5, 4, 100

2, 5, 2, 200

];

Shock: 2

For Deal 1, 5 <= 4 +2 ...True

For Deal 2, ... False

The result should be: 100

• ###### Re: Set modifier field greater than field and variable

I don't know why but I can only see the Mention and App features. The Attach is not visible.

• ###### Re: Set modifier field greater than field and variable

Check the attached image and qvw

• ###### Re: Set modifier field greater than field and variable

Thank you Sunny it worked! Can you please explain to me what you had to put Deal?

• ###### Re: Set modifier field greater than field and variable

I can, but I think HIC did a great job of explaining this here

Re: Why i am not getting same result with Above function ?

• ###### Re: Set modifier field greater than field and variable

No man like HIC.

Again many thanks!

Bryan

• ###### Re: Set modifier field greater than field and variable

I am glad that my post is shared as reference .. Thank you Sunny Bhai

• ###### Re: Set modifier field greater than field and variable

Try this

Sum({<Deal = {"=Cap <= Rate + \$(vShock)"}>} Balance)

• ###### Re: Set modifier field greater than field and variable

Hi Sunny,

Maybe you can help me with the following. This is a continuation of yesterday's discussion.

I have 3 scenarios. 2 of them worked with the syntax you gave me

Scenario 1: Rate+Shock > Cap

Scenario 2: Rate+Shock < Floor

How do you solve it with 2 conditions

Scenario 3: Floor<Rate+Shock and Rate+Shock<Cap

I tried the below syntaxes:

Sum({<Deal = {"=Floor<(Rate + \$(vShock)) Cap>(Rate + \$(vShock))"}>} Balance)

Sum({<Deal = {"=Floor < Rate + \$(vShock) < Cap"}>} Balance)

Finally,

Sum({<Deal = {"=Floor < Rate + \$(vShock)"}, Deal = {"=Cap > Rate + \$(vShock)"}>} Balance).

This last one returned a result but not the one i want as i need the set to be evaluated on the same row. A rate has one cap and one floor only

Many Thanks

• ###### Re: Set modifier field greater than field and variable

Try this

Sum({<Deal = {"=Floor < Rate + \$(vShock) and Cap > Rate + \$(vShock)"}>} Balance)

• ###### Re: Set modifier field greater than field and variable

Works like a charm

Thank you Sunny

• ###### Re: Set modifier field greater than field and variable

sum ({<Cap={"<=\$(=Rate + \$(vShock))"}>} Balance)

You can't have a naked field* on the RHS of a set expression. As the expression is calculated before the table is built, [Rate] will evaluate to null at this point since there is more than one possible value.

You can do this with a Sum(If()) statement - although this may perform poorly if your data set is not small. You can set a flag in the load script - although if vShock is intended to be dynamic, then the load script is not really an option. Finally, if there is some identifying field in the model that ties the Cap and Rate fields, you could use that in your set expression. Edit - this last is the option Sunny has proposed.

A sum(if()) implementation would be

Sum(If(Cap <= Rate + \$(vShock), Balance))

or even simpler (if vShock is a number):

Sum(If(Cap <= Rate + vShock, Balance))

*A naked field reference is a reference to a field outside of an aggregation function (like Sum(), Avg(), Max()).

• ###### Re: Set modifier field greater than field and variable

1)I'm actually changing from SUM(IF()) to set modifier to gain performance.

2)vShock is dynamic yes.

3) Cap and Rate are linked and in the same table. Rate is the interest rate on an account. Cap is the maximum interest rate on that same account

4) Although it seems very interesting, I'm not sure I fully understand the naked field concept. Is the following expression (that works) a naked field example: sum({<fiedl1={\$(=field2)}>}balance)

Thank you