Calculate difference between feilds of a column

[sujit_nath]

I am loading multiple fields, YEAR being one of them. YEAR contains 2 values, 2018 and 2019. I am showing sum(sales) as an expression in straight table. Now I want to show the difference of sales in 2 years. How do I do that? I can not use set expression as these years are dynamic. Somehow the data needs to look like - 

Other dimensions	YEAR	2018	2019	difference
	200	100	100
	300	250	50

How shall I achieve this.

[zhadrakas]

Try to use something like this:

Normal table (bottom - top)
sum(sales) - below(sum(sales)) 
sum(sales) - above(sum(sales)) 
Pivot: (left - right)
sum(sales) - before(sum(sales)) 
sum(sales) - after(sum(sales)) 

regards

tim

[sujit_nath]

I am unable to do that  because I have only one expression, and multiple dimensions. The column YEAR is a dimension. Where do I try the options you mentioned?

The data needed is as:

2018	2019	diff (This is calculated one)
100	50	50
200	100	100

2018 and 2019 are values for year.

[zhadrakas]

you need to create another expression like "absolute difference".

there you try the things i mentioned.

regards

tim

[sujit_nath]

Basically in the year feild, I want one more value as 2018-2019.  Tried with creating a different expression, it shows 2 columns with difference. One for 2018 and anathor for 2019

[Frank_S]

If you simply need the difference between two columns, you can:

• edit chart and add a new expression
• open the expression editor
• insert your expression: =[Total Sales]-[Gross Profit]
• name the column: ie: Net Sales
• apply and save the chart

Not sure if this is what you are after or not but hope it guides you in the right direction.

[sujit_nath]

Hi Frank, Thanks for the solution, but the issue is that I have only one expression. The values I see on chart are under a dimension containing 2 values. 

[sujit_nath]

 Hi Frank, This is how my data looks like (dummy data due to restrictions)

The only expression is sum(sales) and I want to add a difference column beside Q1 and Q2, that will tell the difference in sales made in both quarters

[zhadrakas]

check this thread

https://community.qlik.com/t5/QlikView-App-Development/Dimension-quot-difference-quot-in-a-pivot-tab...

for a workaround where you use the total row to show the difference.

if(Seconddimensionality()=1, Expression for Total, Normal Expression)

You can create a new expression with Seconddimensionality() to see which number you need to use for the if condition:

[Brett_Bleess]

You mentioned a straight table early on, but this appears to be a pivot table, but I am going to toss out the following to have a look just in case it might work for you:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett