# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Create 3 columns in Pivot Chart using expressions for Dimensions / Measures

Hi Experts,

Below are the Dataset and the target Output I am trying to achieve without using CrossTables.

Dataset contains values for 3 companies across 6 columns (2 each for new vs original).

 cash original cash new debt original debt new ebitda original ebitda new Company 1 100 200 50 120 30 190 Company 2 200 400 100 240 60 380 Company 3 400 800 200 480 120 760

I want to create the below output being displayed based on the Company selected from the filter. Below is the output if Company 1 is selected from the filter.

 Original value New value Cash 100 200 Debt 50 120 Ebidta 30 190

I used the following Dimension / Measure combination to achieve the first 2 columns but I am not able to figure out how to add the third column.

Dimension:

ValueList('Cash', 'Debt', 'Ebitda')

Measure:

If(ValueList('Cash', 'Debt', 'Ebitda') = 'Cash',[cash original],

If(ValueList('Cash', 'Debt', 'Ebitda') = 'Debt',[debt original],

If(ValueList('Cash', 'Debt', 'Ebitda') = 'Ebitda',[Ebitda original])))

This gives me the following Output

 Original value Cash 100 Debt 50 Ebidta 30

How do I go about getting the second column as well to the right?

Attached is the sample dataset for reference.

Thanks!

Labels (6)

• ### Visualization

2 Solutions

Accepted Solutions
Master

there must be something missing here, why cant you just create a new measure that does a similar test:

=sum(pick(match(ValueList('Cash', 'Debt', 'Ebitda'),'Cash', 'Debt', 'Ebitda'),[cash new],[debt new],[ebitda new]))

Champion III

You can simplify your visualizations and expressions by transposing the data

crosstable(type,Value,1)
temp:
company,cashoriginal,cashnew,debtoriginal,debtnew,ebitdaoriginal,ebitdanew
Company 1,100,200,50,120,30,190
Company 2,200,400,100,240,60,380
Company 3,400,800,200,480,120,760
];

Main:
,pick(WildMatch(type,'Cash*','Debt*','ebitda*'),'Cash','Debit','Ebitda') as Type1
,pick(WildMatch(type,'*new','*original'),'New','Original') as Type2
,Value
Resident temp;

drop table temp;

exit Script;

Using a pivot table

Type1 and Row Dimension

Type2 and Column Dimension

Measure

=sum(Values)

or Straight Table

Dimension= Type1

New Value = sum({<Type2={'New'}>} Value)

Original  Value = sum({<Type2={'Original'}>} Value)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
8 Replies
Master

there must be something missing here, why cant you just create a new measure that does a similar test:

=sum(pick(match(ValueList('Cash', 'Debt', 'Ebitda'),'Cash', 'Debt', 'Ebitda'),[cash new],[debt new],[ebitda new]))

Contributor III
Author

@edwin thank you so much for your response.

When I tried to create a new measure using the syntax that I had mentioned above, it did not create a new column but just added the new values on top of the existing ones.

Your way of test works. Thanks!

Champion III

You can simplify your visualizations and expressions by transposing the data

crosstable(type,Value,1)
temp:
company,cashoriginal,cashnew,debtoriginal,debtnew,ebitdaoriginal,ebitdanew
Company 1,100,200,50,120,30,190
Company 2,200,400,100,240,60,380
Company 3,400,800,200,480,120,760
];

Main:
,pick(WildMatch(type,'Cash*','Debt*','ebitda*'),'Cash','Debit','Ebitda') as Type1
,pick(WildMatch(type,'*new','*original'),'New','Original') as Type2
,Value
Resident temp;

drop table temp;

exit Script;

Using a pivot table

Type1 and Row Dimension

Type2 and Column Dimension

Measure

=sum(Values)

or Straight Table

Dimension= Type1

New Value = sum({<Type2={'New'}>} Value)

Original  Value = sum({<Type2={'Original'}>} Value)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor III
Author

Is there a way to use dynamic items in the ValueList() ?

One of the items in my actual data table changes based on the Company that is selected from the filter.

Master

you may want to post sample data and what the output should be.   you can add more than these 3 items and your measure will calculate the correct values assuming your data is modelled correctly.

Contributor III
Author

Thanks @edwin, please see the table below. Its just an extension to the one I had shared on the base problem

 cash original cash new debt original debt new ebitda original ebitda new C1 C2 C3 Company 1 100 200 50 120 30 190 Good 1 Good 2 Good 3 Company 2 200 400 100 240 60 380 Bad 1 Bad 2 Bad 3 Company 3 400 800 200 480 120 760 Average 1 Average 2 Average 3

If I want to get the same output as before for Company 1 but instead of Cash, Debt and EBITDA I want to show row labels as Good 1, Good 2, Good 3. So when I change my filter to Company 2 these also update accordingly.

For company 1 the output I am looking for is:

 Original value of cash, debt and ebitda New value of cash, debt and ebitda Good 1 100 200 Good 2 50 120 Good 3 30 190

Essentially a table without having to use the dimension.

Attached is the dataset.

Thanks!

Master

use the same solution.  instead of using the strings, Cash, Debt, Ebitda, use whatever values you want and control what gets aggregated in your measures.

=sum(pick(match(ValueList('Good1', 'Good2', 'Good2'),'Good1', 'Good2', 'Good2'),[cash new],[debt new],[ebitda new]))

Contributor III
Author

But they still need to be strings isn't it?

It does not allow to use field values inside ValueList. For instance I tried this:

=sum(pick(match(ValueList([C1], [C2], [C3]),[C1], [C2], [C3]),[cash new],[debt new],[ebitda new]))

But it says ValueList can only contain non changing parameters.