count and sum with distinct

[robmarr789]

I am trying to write two expressions that will output the following based on the date below.

I've detailed the expected output based on the two conditions

Product code	Final cost	L12 £
98/12	0.02	200
98/12	0.02	200
98/13	200	569
98/14	300	789
98/15	void cost	600
							Output
	Count No. of unique product codes where final cost <> "void cost"	Result	3
	Sum of L12 £ for unique product codes with where final cost <> "void cost"	1558

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {<[Final cost] -= {'void cost'}>} [Product code])

or

Count(DISTINCT {<[Product code] = e({<[Final cost] -= {'void cost'}>})>} [Product code])

and for the other expression

Sum(Aggr(Sum(DISTINCT {<[Final cost] -= {'void cost'}>} [L12 £]), [Product code])

or

Sum(Aggr(Sum(DISTINCT {<[Product code] = e({<[Final cost] -= {'void cost'}>})>} [L12 £]), [Product code])

[Brett_Bleess]

Rob, did Sunny's post do the trick for you?  If so, do not forget to come back and use the Accept as Solution button on his post to mark it as correct and give him credit for the help with things.  If you did something different, consider posting that and mark it, and if you are still working on things, leave an update for us.

Regards,
Brett