Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.