Set modifier field greater than field and variable

[Anonymous]

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

[lironbaram]

hi

what exactly is the problem , you get unexpected results

or you get no results at all?

[Anonymous]

Rather no results

[Chanty4u]

try this

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

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

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

[sunny_talwar]

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)

[jonathandienst]

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()).

[Anonymous]

Didnt Work. Will upload a QVW

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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