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

    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.

          • Re: Set Analysis - variable in modifier
            Jonathan Dienst

            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,

                 

                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