Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Creator III

## Calculate difference between feilds of a column

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.

Labels (3)

• ### Straight Chart Expressions

9 Replies
Specialist II

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

Creator III
Author

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.

Specialist II

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

there you try the things i mentioned.

regards

tim

Creator III
Author

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

Support

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.

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Creator III
Author

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.

Creator III
Author

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

Specialist II

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:

Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Community Browser