2 Replies Latest reply: Jun 13, 2012 6:32 PM by Ron Ventura RSS

    Dynamically modify expressions

    Ron Ventura

      I have a table that includes sales figures for the last 48 months. The fields are called CYM1, CYM2,...CYM48, with CYM1 being last month's data, and CYM48 being data from 48 months ago. I want my user to be able to select a year and month, and then display Yearly totals going back 3 years from the selected year/month.

       

      So, if they select May 2012

       

                YR          Would be the total of CYM1 to CYM5

                YR-1       Would be the total of CYM6 to CYM17

                YR-2       Would be the total of CYM18 to CYM29

       

      However, if they select March 2012

       

                YR          Would be the total of CYM3 to CYM14

                YR-1       Would be the total of CYM15 to CYM26

                YR-2       Would be the total of CYM27 to CYM38

       

      How can I change the expression on the fly, depending on the selection made by the user?

       

      Thanks

        • Re: Dynamically modify expressions
          Giampiero Cina

          Hi,

          try using these functions:

           

          - GETFIELDSELECTIONS()

          -ONLY()

           

          Those functions can help you.

          Hope it helps

          Giampiero

            • Re: Dynamically modify expressions
              Ron Ventura

              Hi Giampiero.

               

              Thanks for your reply, but I think you misunderstood me. I'm fine with the selections, but need to alter the expressions, depending on what those selections are. As a simple example:

               

              If the user selects June 2012, the expression for a particular column might be CYM1+CYM2+CYM3 (returning the last three months). But if they choose April 2012, the expression for that same column woiuld need to be CYM3+CYM4+CYM5 (the three months up to April). CYM1, CYM2 etc are columns in my fact table.

               

              I'm trying to avoid long, messy IF statements.

               

              Thanks