Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MarioCenteno
Creator III
Creator III

Calculate difference two columns pivoting table only 1 dimension qlik sense

Hi Experts! I need your help to calculate difference or variation of two columns pivoting table only 1 dimension qlik sense.

I need to add another column with the official team variation and backup.

 

Dimension Row

Company

Column

equipment

Measure

Sum(Sales)

 

qlik sense ask.png

8 Replies
Channa
Specialist III
Specialist III

=Sum({<equipment={'officer'}>} sales)-Sum({<equipment={'backrest'}>} sales)

 

try this as ur variance

Channa
MarioCenteno
Creator III
Creator III
Author

It has not worked for me is not calculating correctly the variation, in the green box the variation should be -3 of the backup

 

qlik sense ask 2.png

Channa
Specialist III
Specialist III

before(sum(Value))-sum(Value)

Channa
MarioCenteno
Creator III
Creator III
Author

It worked for me changing before for after, but I need the variation to be added as a 3 column.

 

qlik sense ask 3.png

MarioCenteno
Creator III
Creator III
Author

It has stayed like this but I do not want to show it like that.

 

qlik sense ask 4.png

Channa
Specialist III
Specialist III

try to make table instead of pivot or try ad them as columns

Channa
kaanerisen
Creator III
Creator III

Hi guitartrova,

If you need to add a custom column to  pivot table, you can do it by using "valuelist" function to add custom values.

Dimension Row : Company

Column : ValueList('officer','backrest','Var')

Measure : 

pick(match(ValueList('officer','backrest','Var'),'officer','backrest','Var'),
sum({<equipment={'officer'}>}value),
sum({<equipment={'backrest'}>}value),
(sum({<equipment={'officer'}>}value)-sum({<equipment={'backrest'}>}value))
)

Output :

Untitled.png

Hope it helps,

 

ValeriaBonini
Partner - Creator
Partner - Creator

Hi, do you know how to set the Var background color in this case? 
(if Var<0, red, green)