Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bryandandan
New Contributor II

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

1 Solution

Accepted Solutions

Re: Set modifier field greater than field and variable

Try this

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

22 Replies
lironbaram
Honored Contributor II

Re: Set modifier field greater than field and variable

hi

what exactly is the problem , you get unexpected results

or you get no results at all?

bryandandan
New Contributor II

Re: Set modifier field greater than field and variable

Rather no results

sureshqv
Esteemed Contributor III

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

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)

MVP
MVP

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

bryandandan
New Contributor II

Re: Set modifier field greater than field and variable

Didnt Work. Will upload a QVW

bryandandan
New Contributor II

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?

bryandandan
New Contributor II

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

Community Browser