Transpose groups and measures

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello QV-community.

I am new to QV so I must excuse myself for this perhaps stupid question: I want to create a Stacked Bar Chart with measures on the X-axis and the dimension on the Y-Axis. The Dimension is boolean field whcih crosses over all measures (see the first picture). So I want to split my measures in the red and the blue group which is a fact attribute. When I do this in QV I get the QV to Transpose the results in the wrong way (see picture 2).

(How it looks like in Excel)

The Problem here in this picture is that the QV chooses the dimension (1 and 0) as X-Axis instead of grouping togheter respectively measure together and having one stacked gray bar and one stacked blue bar etc.

Re: Transpose groups and measures

juraj_misina — Fri, 05 Oct 2018 15:10:12 GMT

It is not that straight forward, because you need to move your measures to the Dimnesions tab somehow. There are two ways to achieve this.

1. You can adjust your data model and based on your measure definition create new field, which will be used as dimension (this can be done perhaps by use of a few if statements). Then you can add this field as a dimension to you chart.

2. If you can't change data model, you can use ValueList() function to achieve this, but this can get pretty complicated, performance heavy (based on data volume) and rather hard to maintain.

Add a calculated dimension:

ValueList('Expression 1 Label', 'Expression 2 Label','Expression 3 Label')

Then use a single expression:

Pick(Match(ValueList('Expression 1 Label', 'Expression 2 Label','Expression 3 Label'), 'Expression 1 Label', 'Expression 2 Label','Expression 3 Label'),
  Sum(Expression1),
  Sum(Expression2),
  Sum(Expression3)
)

Hope this helps.

Juraj

topic Re: Transpose groups and measures in QlikView

Transpose groups and measures

Re: Transpose groups and measures