topic count and sum with distinct in QlikView

count and sum with distinct

robmarr789 — Sat, 16 Nov 2024 02:14:41 GMT

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

Re: count and sum with distinct

sunny_talwar — Fri, 11 Oct 2019 11:56:20 GMT

May be this

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

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

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

Re: count and sum with distinct

Brett_Bleess — Wed, 16 Oct 2019 14:00:29 GMT

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