Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ArrogantAardvark
Contributor III
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:

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

Accepted Solutions
marcus_sommer
MVP & Luminary
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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
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

View solution in original post

ArrogantAardvark
Contributor III
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.