Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

9 Replies
Not applicable
Author

Can u join a .qvw to test please ?

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

It's look right for me.

Not applicable
Author

Thanks for your reply.

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

Hi Jonathan,

thanks for your reply. You are partially right:

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

Not applicable
Author

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

Thanks

Not applicable
Author

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

s_uhlig
Partner - Creator
Partner - Creator

Hi,

what's about

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

Not applicable
Author

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