Expression for sum (if contains text)

[yvonne-c]

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

[Kushal_Chawda]

Create a chart

Dimension - Job ID

Expression-

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

[yvonne-c]

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

[ahaahaaha]

Hi Yvonne,

Try

Dimension:

JobID

Expression:

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

Result

Regards,

Andrey

[yvonne-c]

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)

[annafuksa1 · Accepted solution]

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

[Kushal_Chawda]

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)

[muthukumar77]

Refer this app,

[Anonymous]

Hi ,

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

Exp

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

Thanks

Paridhi

[Anonymous]

I have attached qvw also