
Re: Problem with a pivot table
Felip Drechsler Dec 14, 2017 12:39 PM (in response to Gloria Bertini)Hi Gloria,
You can use the Column() statement to get the difference of what you want.This statement lets you get the column related measure you want, by using it with an integer, say Column(1) gets the first column of your pivot table.
I have a simple example with two measures and have used the following:
Column(2)Column(1)
This gets me the following (dummy data):
Felipe.

Re: Problem with a pivot table
omar bensalem Dec 14, 2017 2:42 PM (in response to Gloria Bertini)can you maybe show us an image of what u have and what u excpect to add?

Re: Problem with a pivot table
Adwitiya Borah Dec 15, 2017 1:36 AM (in response to Gloria Bertini)Hi Gloria,
You can use Set Analysis here.
Eg: Diff. in Order Quantity =
sum({<[season]={'Summer 2017'}>}[orders quantity])  sum({<[season]={'Summer 2016'}>}[orders quantity])
Let me know if that was helpful!

Re: Problem with a pivot table
Gloria Bertini Dec 15, 2017 3:24 AM (in response to Gloria Bertini)Hi everybody, thanks to all for help, this is what i whant:
I have the pivot table in the left where: 'misure' are measures while 'stagione' are season.
What i want is to add the two column on right ('difference beetwen col. 2 and col. 1', and 'difference beetwen col. 2 and col. 1 all divided by col.1'), but i don't know if it's possible because these are measures, and as far as i know measures can stay only in columns or in rows.
By Now i solved created all single KPI, but i would like to know if there is another way to solve the problem.

Re: Problem with a pivot table
omar bensalem Dec 15, 2017 7:42 AM (in response to Gloria Bertini)1) Make sure to put your measure as rows and dimension as column
2) Activate the total in ur dimension : Stagione :
per analogy :
3) in each of your expression; alter it from:
sum(Measure1)
to
if(Dimensionality()=0,sum({<Stagione={'16E  ESTIVO 2017}>}Measure1)sum({<Stagione={'16E  ESTIVO 2016}>}Measure1) , sum(Measure1))
sum(Measure2)
to
if(Dimensionality()=0,sum({<Stagione={'16E  ESTIVO 2017}>}Measure1)sum({<Stagione={'16E  ESTIVO 2016}>}Measure1) , sum(Measure2))
sum(Measure3)
to
if(Dimensionality()=0,sum({<Stagione={'16E  ESTIVO 2017}>}Measure1)sum({<Stagione={'16E  ESTIVO 2016}>}Measure1) , sum(Measure3))
sum(Measure4)
to
if(Dimensionality()=0,sum({<Stagione={'16E  ESTIVO 2017}>}Measure1)sum({<Stagione={'16E  ESTIVO 2016}>}Measure1) , sum(Measure4))
and so on..
AND PLEASE MAKE SUR TO WRITE THE VALUES OF UR DIMENSION EXACTLY AS THEY ARE .
Qlik is case sensitive
Result (per analogy)


Re: Problem with a pivot table
Andrea Gigliotti Dec 15, 2017 4:52 AM (in response to Gloria Bertini)I think you should follow what adwitiya said above.
First create a variable named "vMisura" as below:
if( Misure = 'Ordini Totali', 'your_field_name_for_order_number',
if( Misure = 'Quantità Spedita', 'your_field_name_for_qta_delivery',
....
for all your dimension values (it seems to be thirtteen)
finally create four measures on your pivot table as below:
sum( {< [season] = {'summer 2016'} >} $(vMisura) )
sum( {< [season] = {'summer 2017'} >} $(vMisura) )
Column(2)Column(1)
(Column(2)Column(1)) / Column(1)
I hope it helps.

Re: Problem with a pivot table
kaan erisen Dec 15, 2017 6:39 AM (in response to Gloria Bertini)Hi,
sampledata:
load * Inline [
YEAR,VALUE1,VALUE2,VALUE3
2010,300,100,50
2010,400,250,25
2010,200,140,70
2011,700,400,300
2011,500,200,300
2011,600,450,170
];
create a variable named 'vColumnList':
ValueList('2010','2011','Diff','Ratio')
Add Pivot Table to your sheet:
Column :
$(vColumnList)
Measure1(Value1) :
pick(Match(ValueList('2010','2011','Diff','Ratio'),'2010','2011','Diff','Ratio'),
sum({<YEAR={2010}>}VALUE1),
sum({<YEAR={2011}>}VALUE1),
sum({<YEAR={2011}>}VALUE1)sum({<YEAR={2010}>}VALUE1),
num((sum({<YEAR={2011}>}VALUE1)sum({<YEAR={2010}>}VALUE1))/sum({<YEAR={2011}>}VALUE1),'#,##%')
)
Measure2:
pick(Match(ValueList('2010','2011','Diff','Ratio'),'2010','2011','Diff','Ratio'),
sum({<YEAR={2010}>}VALUE2),
sum({<YEAR={2011}>}VALUE2),
sum({<YEAR={2011}>}VALUE2)sum({<YEAR={2010}>}VALUE2),
num((sum({<YEAR={2011}>}VALUE2)sum({<YEAR={2010}>}VALUE2))/sum({<YEAR={2011}>}VALUE2),'#,##%')
)
Measure3:
pick(Match(ValueList('2010','2011','Diff','Ratio'),'2010','2011','Diff','Ratio'),
sum({<YEAR={2010}>}VALUE3),
sum({<YEAR={2011}>}VALUE3),
sum({<YEAR={2011}>}VALUE3)sum({<YEAR={2010}>}VALUE3),
num((sum({<YEAR={2011}>}VALUE3)sum({<YEAR={2010}>}VALUE3))/sum({<YEAR={2011}>}VALUE3),'#,##%')
)
You can edit the expressions to work for your data model.
Hope it helps..

Re: Problem with a pivot table
Gloria Bertini Dec 15, 2017 8:40 AM (in response to Gloria Bertini)thanks a lot to everyone for the help.
As soon as i can i'll try all the differents solutions
Good day to all