topic Re: Using Distinct to sum multiple data entries in QlikView

Using Distinct to sum multiple data entries

Mon, 04 Feb 2013 16:05:39 GMT

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?

Re: Using Distinct to sum multiple data entries

Mon, 04 Feb 2013 16:33:37 GMT

Hi,

See attach file if it's what you're looking for

Re: Using Distinct to sum multiple data entries

Tue, 05 Feb 2013 08:58:14 GMT

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

Re: Using Distinct to sum multiple data entries

Tue, 05 Feb 2013 09:09:37 GMT

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