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

    Dynamically modify expressions

      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?



        • Re: Dynamically modify expressions


          try using these functions:





          Those functions can help you.

          Hope it helps


            • Re: Dynamically modify expressions

              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.