Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Using Distinct to sum multiple data entries

Hi there

I have some data that looks like this

HESAProject CodeApplication ValueStaff Name
CharityPR11000Dave
CharityPR11000Keith
CharityPR11000Bruce
Research CouncilPR25000Bruce
Research CouncilPR25000Dave

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?

3 Replies
Not applicable

Re: Using Distinct to sum multiple data entries

Hi,

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

Not applicable

Re: Using Distinct to sum multiple data entries

Hi, thank you.

From the data I gave that would work, but what about this

HESAProject CodeApplication ValueStaff Name
CharityPR11000Dave
CharityPR11000Keith
CharityPR11000Bruce
Research CouncilPR25000Bruce
Research CouncilPR25000Dave
CharityPR31000Steve
CharityPR31000Diane

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

Not applicable

Re: Using Distinct to sum multiple data entries

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

HESAPROJECTCODE SUM( DISTINCT A_VALUE)
CPR11000
CPR31000
RPR25000
Community Browser