Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sujit_nath
Creator III
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 dimensionsYEAR20182019difference
 200100100
 30025050

 

How shall I achieve this.

Labels (3)
9 Replies
zhadrakas
Specialist II
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

sujit_nath
Creator III
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:

20182019diff (This is calculated one)
1005050
200100100

 

2018 and 2019 are values for year.

zhadrakas
Specialist II
Specialist II

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

there you try the things i mentioned.

regards

tim

sujit_nath
Creator III
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

Frank_S
Support
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.

Calculated Column.PNG

We are just 'like' you and like to be liked when providing a helpful answer. You may also press the 'Solution Accepted' button if an answer provided resolves your question or issue... Cheers!
sujit_nath
Creator III
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. 

sujit_nath
Creator III
Creator III
Author

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

Capture.PNG

 

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
Specialist II
Specialist II

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
Employee
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.