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

[saurabh_Qlik1]

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!

[edwin · Accepted solution]

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

[saurabh_Qlik1]

@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 · Accepted solution]

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)

[saurabh_Qlik1]

@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]

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.

[saurabh_Qlik1]

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]

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

[saurabh_Qlik1]

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.