9 Replies Latest reply: May 28, 2012 3:01 AM by lollo figo

# Set Analysis - variable in modifier

Hi all,

hope you can help on this one. I have the following field: YEAR_SELECTION which can get the following values: "financial_year" or "underwriting_year".

Each of the two values corresponds to a field that has been loaded. Based on the YEAR_SELECTION field, I have computed a variable (YEAR) which, based on the active selection in YEAR_SELECTION provides the name of the field that contains the years I want to look at.

What I'm trying to do is to sum revenues for just the max(YEAR).

Therefore what I want is something like:

=sum({ < YEAR_SELECTION = {\$(=max(\$(YEAR)))} } REVENUES)

The formula works partially. It works if I write:

=sum({ <financial_year = {\$(=max(\$(YEAR)))} } REVENUES)

or

=sum({ <financial_year = {\$(=max(\$(YEAR)))} } REVENUES)

but I cannot get the variable YEAR_SELECTION to become the modifier for the set analysis.

Any one able to solve this?

Suggestions are veery welcomed

Thanks

• ###### Re: Set Analysis - variable in modifier

Can u join a .qvw to test please ?

Try =sum({ < \$(YEAR_SELECTION) = \$(=max(\$([YEAR]))) > } REVENUES)

It's look right for me.

• ###### Set Analysis - variable in modifier

I have tried your solution but it doesen't work. If QV can't recognize the YEAR_SELECTION field then it will sum up everything, disregarding the year.

I can't attach a qvw file since I'm using a personal edition and wouldn't be able to reopen the file again.

Sorry

• ###### Set Analysis - variable in modifier

I will sue ur qvw to test easily. I will past the functionnal code here

• ###### Re: Set Analysis - variable in modifier

Hi

I am not sure why you want YEAR_SELECTION in the set expression at all. From your description I understood that YEAR_SELECTION is the variable a user selects to choose between underwriting or financial year. When the user makes that selection, the YEAR variable updates to reflect the correct field name. This field contains a value that you want to test against financial_year in the set expression.

Am I correct so far? Then your set expression appears to work in the way that you want...

So then I am not sure that I understand your problem. Please clarify

Regards

Jonathan

• ###### Set Analysis - variable in modifier

Hi Jonathan,

YEAR_SELECTION is the variable a user selects to choose between underwriting or financial year. When the user makes that selection, the YEAR variable updates to reflect the possible values available for the field selected.

Let me make an example, hopefully this can help:

say you selected financial_year in the YEAR_SELECTION field. The field YEAR would return 2011, 2012. This means that the sum of revenues I'm trying to do now would be:

=sum({ <financial_year = {'2012'}>} REVENUES)

other example:

say you selected undewriting_year in the YEAR_SELECTION field. The field YEAR would return 2007, 2008, 2009. This means that the sum of revenues I'm trying to do now would be:

=sum({ <undewriting_year = {'2009'}>} REVENUES)

The issue is that the formula has to adopt by itself based on the selection done in YEAR_SELECTION and therefore compute the max year available from the possible values of the outcome field.

Both explenation and solutions are hard. Sorry

thanks

• ###### Re: Set Analysis - variable in modifier

I'm attaching a little file that hopefully will be helpful.

Thanks

• ###### Set Analysis - variable in modifier

Sounds like what you need is an IF statement, really:

if(\$(YEAR_SELECTION) = 'financial_year',

sum({ <financial_year = {\$(YEAR)}>} REVENUES),

sum({ <undewriting_year = {\$(YEAR)}>} REVENUES))

• ###### Re: Set Analysis - variable in modifier

Hi,

```sum({ < \$(YEAR) = {\$(=max(\$(YEAR)))} >} REV)
```

?

But I would suggest either to

```load * inline [
YEAR_SELECTION
FIN_YEAR
UW_YEAR
];
```

or

```load FIN_YEAR as [Financial Year], UW_YEAR as [Undewriting Year] ...
```

which will simplify the situation.

Regards

Sven

• ###### Re: Set Analysis - variable in modifier

Hi all and thanks for your repies.

Yes, the IF statement works but I would need to have a condition for every YEAR_SELECTION I have. I don't really like this solution but can't think of anything better. At the moment this is the best I could get to.

"Computing" the YEAR_SELECTION inside the set_analysis as the name of the field is not possible.

Thanks anyway