Friday, October 3, 2008

Expression Knows Which Cycle Group Field is Active

Someday you may have a chart like this: there's a cycle group in the chart dimension and all of the expressions work great except that one expression needs to work differently when one particular field of the cycle group is active. Here's an example-- your chart for current month sales is working ok but the expression for allocated sales revenue must work differently when the cycle group field Division is used. Maybe the accounting department has decreed that a complex, ever changing formula must be used to show allocated sales revenue by Division so your report needs simply to show 'Not Defined' in the allocated sales revenue column when Division is used for the dimension.
You can write the expression something like this:
If(GetCurrentField(CorpCycle)='Division','Not Defined',Sum(alloc_sales))

In this example, CorpCycle is the name of the cycle group. Division is the name of the cycle group field. Use the actual field name not the label that might be used in the chart. If you're not familiar with using a cycle group in the chart dimension then it will be worth your time to find the reference manual or tutorial and read about it. Being able to change the chart dimension instantly with a mouse click is a powerful feature.

If you build your expressions using the wizard available on the Edit Expression window, you will find the GetCurrentField function along with several other interesting functions listed under System Functions.
Another common place to use this function is in the chart title. For example, you might use this for a chart title:
='CURRENT MONTH SALES SUMMARY BY ' & GetCurrentField(CorpCycle)
That will use the currently active field name from the cycle group in the chart title. As a user clicks on the cycle group the chart title will automatically change along with the dimension.

2 comments:

Marta said...

Does this function account for more than one dimension in the cycle group? For example, I want to apply different mapping table for each of the four dimensions in the cycle group.

-TB said...

When someone at work asks me a question like that, my answer is always "Try it and see".
You will find that the function returns the name of the field that was defined in the cycle group and which has been chosen (or the expression text if it is a calculated dimension in the cycle group).
You can extra logic to your expression to modify the field name or map it to something else if you like but the GetCurrentField function will work just as described in the QlikView Help text.