topic Re: Calculate difference between feilds of a column in QlikView

Calculate difference between feilds of a column

sujit_nath — Sat, 16 Nov 2024 01:47:08 GMT

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.

Re: Calculate difference between feilds of a column

zhadrakas — Wed, 04 Dec 2019 12:44:27 GMT

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

Re: Calculate difference between feilds of a column

sujit_nath — Wed, 04 Dec 2019 13:49:10 GMT

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.

Re: Calculate difference between feilds of a column

zhadrakas — Wed, 04 Dec 2019 13:53:34 GMT

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

there you try the things i mentioned.

regards

tim

Re: Calculate difference between feilds of a column

sujit_nath — Wed, 04 Dec 2019 14:14:27 GMT

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

Re: Calculate difference between feilds of a column

Frank_S — Wed, 04 Dec 2019 20:31:40 GMT

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.

Re: Calculate difference between feilds of a column

sujit_nath — Thu, 05 Dec 2019 03:41:27 GMT

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.

Re: Calculate difference between feilds of a column

sujit_nath — Thu, 05 Dec 2019 05:05:05 GMT

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

Re: Calculate difference between feilds of a column

zhadrakas — Thu, 05 Dec 2019 11:58:44 GMT

check this thread

https://community.qlik.com/t5/QlikView-App-Development/Dimension-quot-difference-quot-in-a-pivot-table/td-p/466192

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:

Re: Calculate difference between feilds of a column

Brett_Bleess — Wed, 11 Dec 2019 19:49:18 GMT

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/InterRecordFunctions/column.htm

Regards,
Brett