topic Set Analysis - variable in modifier in QlikView

Set Analysis - variable in modifier

Fri, 25 May 2012 13:45:40 GMT

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)

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

Fri, 25 May 2012 14:03:50 GMT

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

Fri, 25 May 2012 14:28:39 GMT

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

Re: Set Analysis - variable in modifier

jonathandienst — Fri, 25 May 2012 14:30:54 GMT

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

Fri, 25 May 2012 14:50:51 GMT

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

Set Analysis - variable in modifier

Fri, 25 May 2012 14:56:56 GMT

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

Re: Set Analysis - variable in modifier

Fri, 25 May 2012 15:20:12 GMT

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

Thanks

Set Analysis - variable in modifier

Fri, 25 May 2012 20:54:19 GMT

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

s_uhlig — Sat, 26 May 2012 13:43:54 GMT

Hi,

what's about

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

But I would suggest either to

load * inline [
YEAR_SELECTION
FIN_YEAR
UW_YEAR
];

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

which will simplify the situation.

Regards

Sven

Re: Set Analysis - variable in modifier

Mon, 28 May 2012 07:01:26 GMT

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