topic Dynamically modify expressions in QlikView

Dynamically modify expressions

Wed, 13 Jun 2012 07:01:20 GMT

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

Wed, 13 Jun 2012 08:44:37 GMT

Hi,

try using these functions:

- GETFIELDSELECTIONS()

-ONLY()

Those functions can help you.

Hope it helps

Giampiero

Re: Dynamically modify expressions

Wed, 13 Jun 2012 22:32:15 GMT

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