Multiple alternate states as a synthetic dimension

[ArrogantAardvark]

Hello, has anyone ever attempted to use multiple alternate states as a dimension, e.g. in a pivot table?

For example, suppose I have three alternate states, and I would like to construct a pivot table with measures along the rows such that each column would contain data for a the corresponding alternate state:

	<default>	<state1>	<state2>
measure1
measure 2
...

The easiest way to do this would be to construct a synthetic dimension composed of the alternate states and then let Qlik do its magic, but I haven't figured out how to get this to work.

One thing I *have* attempted is to create a synthetic dimension via inline load containing the names of the alternate states as strings, and then to write each measure as a paramatized variable, where the value of the synthetic dimension would be read in as a set identifier, like this:

Set mMeasure1 = Sum({$1}MyField)

and then set the pivot table measure equal to:

$(mMeasure1(SyntheticDimensionValue))

...but I haven't gotten this to work either. Tried lots of permutations of dollar sign expansions too.

Ideas??? Much gratitude for any help!

[marcus_sommer · Accepted solution]

I'm not really sure if I understand your approach right but I assume that you want to refer with:

$(mMeasure1(SyntheticDimensionValue))

to the dimensionsvalues of:

$(mMeasure1(State1))
$(mMeasure1(State2))
...

If so it won't work because a $-sign expansion will be executed first and the result of it is then applied to all chart-calculations.

The only way to get such a construct to work is the following:

pick(match(SyntheticDim, 'default', 'state1', 'state2'),
   expr1, expr2, exp3)

- Marcus

[ArrogantAardvark]

Spot on, that's exactly what I'm trying to do. Was hoping to avoid having to code the expression multiple times in a pick list, but I didn't stop to think that a $ expansion would be applied uniformly across a measure. Marking as solved, thanks for the expedient help.