adding 2 values and subtract from select row in Pivot table

[gopikrishnach]

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'

[sunny_talwar · Accepted solution]

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))

[gopikrishnach]

Hi Sunny T,

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

[gopikrishnach]

Dear Sunny T,

Can you please provide the qvw file.

Regards,

Gopikrishna.

[sunny_talwar]

PFA

[gopikrishnach]

Hi Sunny,

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

[sunny_talwar]

Please refer below screenshot and provide the qvw.

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

[gopikrishnach]

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.

[sunny_talwar]

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

[gopikrishnach]

I am uploading the data, please check once.

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