topic Re: adding 2 values and subtract from select row in Pivot table in QlikView

adding 2 values and subtract from select row in Pivot table

gopikrishnach — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have one scenario, i want to create pivot table with 3 dimension and 1 expression. In expression i want to add the previous values by subtracting of current value.

For Example:

I have Company Name, Department, Employee Name, Salary.

Iam creating Pivot table with Company Name, Department, Employee name as dimensions and sum(Salary) as Expression.

Company Name	Department	Employee Name	SUM(Salary)	Condition
ABC	Sales	AA	10000	same
ABC	Production	BB	15000	Salary 'AA'-Salary 'BB'
ABC	Supporting	CC	20000	(salary 'AA'+Salary 'BB')-Salary 'CC'
ABC	Marketing	DD	30000	(salary 'AA'+Salary 'BB'+Salary 'CC')-Salary 'DD'
DEF	Sales	EE	10000	same
DEF	Production	FF	15000	Salary 'EE'-Salary 'FF'
DEF	Supporting	GG	20000	(salary 'EE'+Salary 'FF')-Salary 'GG'
DEF	Marketing	HH	30000	(salary 'EE'+Salary 'FF'+Salary 'GG')-Salary 'HH'

Re: adding 2 values and subtract from select row in Pivot table

sunny_talwar — Sat, 10 Dec 2016 14:19:16 GMT

May be this:

=If(Aggr(RowNo(), [Company Name], [Employee Name]) = 1, Sum(Salary), Aggr(RangeSum(Above(Sum(Salary), 1, RowNo())), [Company Name], [Employee Name])-Sum(Salary))

Re: adding 2 values and subtract from select row in Pivot table

gopikrishnach — Sat, 10 Dec 2016 14:25:51 GMT

Hi Sunny T,

Can you please provide any thing i need to modify in editor and also please provide the script.

Re: adding 2 values and subtract from select row in Pivot table

gopikrishnach — Sat, 10 Dec 2016 14:31:20 GMT

Dear Sunny T,

Can you please provide the qvw file.

Regards,

Gopikrishna.

Re: adding 2 values and subtract from select row in Pivot table

sunny_talwar — Sat, 10 Dec 2016 15:17:59 GMT

PFA

Re: adding 2 values and subtract from select row in Pivot table

gopikrishnach — Sat, 10 Dec 2016 15:21:55 GMT

Hi Sunny,

I have one more question. Please refer below screenshot and provide the qvw.

Re: adding 2 values and subtract from select row in Pivot table

sunny_talwar — Sat, 10 Dec 2016 15:35:34 GMT

Please refer below screenshot and provide the qvw.

I just added the qvw my friend? What other qvw are you looking for?

Re: adding 2 values and subtract from select row in Pivot table

gopikrishnach — Sat, 10 Dec 2016 15:44:38 GMT

I Have another scenario that i want the final result as for dpr 2, (sum(dpr1) day value -dpr2 day value) in the attached screen shot.

Re: adding 2 values and subtract from select row in Pivot table

sunny_talwar — Sat, 10 Dec 2016 16:04:39 GMT

I am sorry, I don't understand things just by looking at the images and a very brief description. Please read these and me or someone else might be able to help you better

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Best,

Sunny

Re: adding 2 values and subtract from select row in Pivot table

gopikrishnach — Sat, 10 Dec 2016 16:18:21 GMT

I am uploading the data, please check once.

please refer the attached spread sheet. i want condition for result field in sheet1