topic Re: How to create Pivot or Straight table with expression results on multiple rows per dimension? in QlikView

How to create Pivot or Straight table with expression results on multiple rows per dimension?

Wed, 25 Nov 2020 16:16:04 GMT

I have loaded a table into Qlik that has the dimension of "Cost Center" and multiple expression calculations that are to be expressed as Goal, Actual and Percent to Goal.

Desired Result:

For each cost center dimension, I would like to display Goal on Top row, Actual in the Middle and % to Goal at the bottom. I do not like having Goal, Actual and % to Goal displayed adjacently on the same row because it's hard to see the relationship between those results, but am open for other display suggestions.

Source Data:

The Goal and actual numbers are brought in from two separate SQL tables and Qlik does the joins on the Cost Center columns. I could split the load into separate statements but not sure what would work best.

Any suggestions for doing this?

Cost Center	Metric1	Metric2
Cost Center 1234	Goal #	Goal #
	Actual #	Actual #
	% to Goal	% to Goal
Cost Center 4321...	Goal #	Goal #
	Actual #	Actual #
	% to Goal	% to Goal

Re: How to create Pivot or Straight table with expression results on multiple rows per dimension?

Anonymous — Tue, 18 Apr 2017 16:40:41 GMT

Can you share a sample with mock data.

Re: How to create Pivot or Straight table with expression results on multiple rows per dimension?

Tue, 18 Apr 2017 16:52:26 GMT

Thank you for your response @ShivaNagesh, I will have to try later to mock up data and make it available here. I am doing the actual calculations in SQL except for the % to Goal.

Here is an attempt for me to convey how the data looks:

From Actuals table

Cost Center 1234,

Metric 1 value: 10,000

Metric 2 value: 5,000

Cost Center 4321

Metric 1 Goal: 20,000

Metric 2 Goal: 10,000

These would be displayed on separate lines, but grouped by the cost center as shown in my OP.

There are more fields to display, but they would be displayed in one row for each dimension value.

If this isn't enough I will endeavor to provide a sample file tomorrow.

Re: How to create Pivot or Straight table with expression results on multiple rows per dimension?

swuehl — Tue, 18 Apr 2017 17:52:37 GMT

You could create a synthetic dimension (using a data island table in the model or ValueList() ) with Goal, Actual and %.

Then use a conditional function like Pick(Match() ) to branch into the specific calculation within your single expression.

There are some examples here in the forum how to do that. Just search for ValueList() and pick() / match().