Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

smith
New Contributor II

Multiple alternate states as a synthetic dimension

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!

Labels (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Multiple alternate states as a synthetic dimension

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

2 Replies
MVP & Luminary
MVP & Luminary

Re: Multiple alternate states as a synthetic dimension

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

smith
New Contributor II

Re: Multiple alternate states as a synthetic dimension

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.