# New to QlikView

Discussion board where members can get started with QlikView.

Contributor

## Expression for sum (if contains text)

Hi

Please can you help me with an expression for the following table.

I want to calculate the cost per "JobID" but only include "JobID" that contain code "VR" ( so the total for job IDs 42067, 42074, 42081)

Many thanks

 JobID Code Price Job Cost 42066 CPMT1 19.2 42066 V1 21 40.2 42067 BOVT 230.4 42067 VR 15 245.4 42069 CPMT1 19.2 42069 V1 21 40.2 42074 TIM 211.2 42074 SYRI01 0.25 42074 VR 15 42074 CDA 172.8 42074 OPMAT2 25 42074 SYRI01 0.25 42074 SYRI02 1.1 425.6 42081 TIM 201.6 42081 VR 15 216.6

Tags (2)
1 Solution

Accepted Solutions
Contributor III

## Re: Expression for sum (if contains text)

try sum( {\$<JobID = P({1<Code={VR}>} JobID)>} Price)

10 Replies
MVP

## Re: Expression for sum (if contains text)

Create a chart

Dimension - Job ID

Expression-

=Sum({<Code={'VR'}>}[Job Cost])

Contributor

## Re: Expression for sum (if contains text)

Thanks Kushal

I want to calculate the complete job cost; sum(price) with JobID as dimension would work fine,

BUT I want to include ALL codes for JobIDs that contain CODE "VR"  - not just the VR code

Honored Contributor

## Re: Expression for sum (if contains text)

Hi Yvonne,

Try

Dimension:

JobID

Expression:

Aggr({<JobID={"=Only({<Code={'VR'}>}JobID)"}>}Sum([Job Cost]), JobID)

Result

Regards,

Andrey

Contributor

## Re: Expression for sum (if contains text)

Thanks Andrey, that looks good but I'm just getting an empty table - am I missing something?

Aggr({<JobID={"=Only({<Code={'VR'}>}JobID)"}>}Sum([Price]),JobID)

Contributor III

## Re: Expression for sum (if contains text)

try sum( {\$<JobID = P({1<Code={VR}>} JobID)>} Price)

MVP

## Re: Expression for sum (if contains text)

instead going for complex expression, try creating flag in scrip

Data:

LOAD JobID,

Code,

Price

FROM

[https://community.qlik.com/thread/255056?sr=inbox&ru=135531]

(html, codepage is 1252, embedded labels, table is @1);

left join

LOAD distinct JobID,

1 as Flag

Resident Data

where Code='VR'

;

Now create chart with below expression

=sum({<Flag={1}>}Price)

Partner

## Re: Expression for sum (if contains text)

Refer this app,

Muthukumar Pandiyan
Contributor

## Re: Expression for sum (if contains text)

Hi ,

You use simple expression to calculate required output,Hope this will help you.

Exp

sum({<Code={'VR'}>}[Job Cost])

Thanks

Paridhi

Contributor

## Re: Expression for sum (if contains text)

I have attached qvw also