6 Replies Latest reply: Feb 24, 2017 10:49 AM by Sunny Talwar

# SUM set analaysis if group contains a certain line

I have a small data set:

 PID PCODE PVAL 2 N50 £1,000.00 2 B10 £100.00 2 P40 £500.00 3 B10 £100.00 3 P40 £500.00 4 B10 £100.00 4 P40 £500.00

I want to have a straight table that shows PID and PCODE and PVAL

Except if a PID has a PCODE of N50 in it, then their B10 needs to be reduced by 5% of the N50 like so:

 PID PCODE PVAL 2 N50 £1,000.00 2 B10 £50.00 2 P40 £500.00 3 B10 £100.00 3 P40 £500.00 4 B10 £100.00 4 P40 £500.00

how can i do this?

• ###### Re: SUM set analaysis if group contains a certain line

May be this:

=If(Count(TOTAL <PID> {<PCODE = {'N50'}>}PCODE) > 0, Sum({<PCODE = {'B10'}>}PVAL)*0.50 + Sum({<PCODE -= {'B10'}>}PVAL), Sum(PVAL))

• ###### Re: SUM set analaysis if group contains a certain line

It works, but if i make some selections if reverts back to the original value

• ###### Re: SUM set analaysis if group contains a certain line

What selections are you making?

• ###### Re: SUM set analaysis if group contains a certain line

Its also only showing people who have the deduction

• ###### Re: SUM set analaysis if group contains a certain line

Unless you show what you have, I am not going to understand what you are saying

• ###### Re: SUM set analaysis if group contains a certain line

or

IF(PID = Only({<PID = P({<PCODE = {'N50'}>}PID)>}PID),SUM({<PCODE = {'B10'}>}PVAL)*(1-'50%')+SUM({<PCODE -= {'B10'}>}PVAL),SUM(PVAL))