Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
try using these functions:
- GETFIELDSELECTIONS()
-ONLY()
Those functions can help you.
Hope it helps
Giampiero
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