Qlik Community

New to Qlik Sense

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

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sau953_Qlik
Contributor III
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)
2 Solutions

Accepted Solutions
edwin
Master
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]))

View solution in original post

vinieme12
Champion III
Champion III

You can simplify your visualizations and expressions by transposing the data

crosstable(type,Value,1)
temp:
load * inline [
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:
Load company
,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.

View solution in original post

8 Replies
edwin
Master
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]))

sau953_Qlik
Contributor III
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! 

vinieme12
Champion III
Champion III

You can simplify your visualizations and expressions by transposing the data

crosstable(type,Value,1)
temp:
load * inline [
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:
Load company
,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.
sau953_Qlik
Contributor III
Contributor III
Author

@edwin and @vinieme12 

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. 

edwin
Master
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.

sau953_Qlik
Contributor III
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!

edwin
Master
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]))

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