Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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?
Hi Dan, maybe with:
=sum({<status_cat={'open'}, problem_cat={'reactive'}>} Aggr([Min/Max/Distinct/...]([WR AveEstimateCost]), [Work Request Code))
distinct wil work for sure
Hi Dan, maybe with:
=sum({<status_cat={'open'}, problem_cat={'reactive'}>} Aggr([Min/Max/Distinct/...]([WR AveEstimateCost]), [Work Request Code))
worked with
=sum({<status_cat={'open'}, problem_cat={'reactive'}>} Aggr(DISTINCT ( [WR AveEstimateCost]), [Work Request Code]))
Thanks!!