little help with expression

Mon, 05 Oct 2015 10:56:51 GMT

I'm trying to sum the total of all work, but I have issue as we have some records which are listed multiple times in a table, as in this example:

Work Request Code	Works Description	Engineer	WR AveEstimateCost
57377	xxxxx	A	£ 64.64
60873	xxxxxx	A	£ 35.77
66287	xxxxxxxx	A	£ 140.83
71689	xxxxxxxxx	A	£ 67.30
73699	sdgfg fgdg	A	£ 67.30
73700	fgfdg hggh h	A	£ 67.30
73697	h dhg hgfhgf hhf	A	£ 67.30
73697	h dhg hgfhgf hhf	B	£ 67.30

we always show duplicate rows where a work request has more than one engineer assigned to it, and generally this is correct in our qlikview model.

but in a text box I'm trying to total up the sum of the work using the following expression:

=sum({<status_cat={'open'}, problem_cat={'reactive'}>} [WR AveEstimateCost])

this gives a result of £577,74. But I only want it to add up for each unique work request code and get the result, which in this example, should be £510.44

I've tried adding Distinct [Work Request Code] into the expression and also use Aggr, but I'm either not getting it in the right place or using the correct syntax/set analysis etc

can anyone help?

Re: little help with expression

gautik92 — Mon, 05 Oct 2015 11:04:16 GMT

distinct wil work for sure

Re: little help with expression

rubenmarin — Mon, 05 Oct 2015 11:05:34 GMT

Hi Dan, maybe with:

=sum({<status_cat={'open'}, problem_cat={'reactive'}>} Aggr([Min/Max/Distinct/...]([WR AveEstimateCost]), [Work Request Code))

Re: little help with expression

Mon, 05 Oct 2015 11:11:38 GMT

worked with

=sum({<status_cat={'open'}, problem_cat={'reactive'}>} Aggr(DISTINCT ( [WR AveEstimateCost]), [Work Request Code]))

Thanks!!

topic Re: little help with expression in QlikView

little help with expression

Re: little help with expression

Re: little help with expression

Re: little help with expression