Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there
I have some data that looks like this
HESA | Project Code | Application Value | Staff Name |
Charity | PR1 | 1000 | Dave |
Charity | PR1 | 1000 | Keith |
Charity | PR1 | 1000 | Bruce |
Research Council | PR2 | 5000 | Bruce |
Research Council | PR2 | 5000 | Dave |
My requirement is to produce the application value for each HESA classification. The problem is that the total for PR1 is actually 1000 (not 3000 by simple sum). It is repeated for each staff member involved.
Basically I need an expression that only sums one value for each project code. I think I can use Distinct in the sum formula for this, but cannot seem to produce a valid expression.
Can anyone help?
Hi,
See attach file if it's what you're looking for
Hi, thank you.
From the data I gave that would work, but what about this
HESA | Project Code | Application Value | Staff Name |
Charity | PR1 | 1000 | Dave |
Charity | PR1 | 1000 | Keith |
Charity | PR1 | 1000 | Bruce |
Research Council | PR2 | 5000 | Bruce |
Research Council | PR2 | 5000 | Dave |
Charity | PR3 | 1000 | Steve |
Charity | PR3 | 1000 | Diane |
I need to get the answer 2000 for Charity and 5000 for Research Council.
Sorry to add to the query, but realised a loop hole in the data and question.
Regards
Rob
A:
LOAD Distinct HESA,
PROJECTCODE,
A_VALUE ,
S_NAME
FROM
(ooxml, embedded labels, table is Sheet1) ;
THEN pivot chart---
dimension--- hesa
projectcode
expression--- SUM( DISTINCT A_VALUE)
then output like this--
HESA | PROJECTCODE | SUM( DISTINCT A_VALUE) |
C | PR1 | 1000 |
C | PR3 | 1000 |
R | PR2 | 5000 |