April 13, 2010

Set Analysis Problem

The problem

To display data in a way that ignores selections, you can use a set expression {1} in any aggregation functions. This is useful for a dashboard page which displays (for example) the last 30 days data. The chart expression could be something like:
=Sum({1} Distinct If(TranDate>=today()-30, TranValue))

I have a chart to display data which must ignore all selections, except one (on a field named ForcedLoadingState, which could contain 'Yes' or 'No'). The problem was how to create a set expression which would ignore all selections except on ForcedLoadingState.

The solution

1. Create a variable (eg vForcedLoadingState).

2. Use the set expression:

3. Define the variable as:


This allows the user to see the data unfiltered by ForcedLoadingState (ie no selection) or for either state of ForcedLoadingState. When ForcedLoadingState is unfiltered, the variable will pass both Yes and No as valid options to the set expression. The '1' in the set expression overrides all other selections.

If(Len(field) = 0,..) is equivalent to If(IsNull(field),..), but I have found the Len approach more reliable. Maybe that's just me :-)

If there are more than two valid states, then the variable expression will need to include all the options in a comma separated list. This will work as long as there is a small number of possible options.

No comments:

Post a Comment