Dynamically modify expressions

[#1]

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

[#2]

Hi,

try using these functions:

- GETFIELDSELECTIONS()

-ONLY()

Those functions can help you.

Hope it helps

Giampiero

[#3]

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