# App Development

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## 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:

 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 (3)

• ### Pivot Tables

1 Solution

Accepted Solutions
MVP & Luminary

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

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

Contributor III
Author

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.

Tags
Community Browser