topic Expression for sum (if contains text) in QlikView

Expression for sum (if contains text)

yvonne-c — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression for sum (if contains text)

Kushal_Chawda — Tue, 28 Mar 2017 14:31:59 GMT

Create a chart

Dimension - Job ID

Expression-

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

Re: Expression for sum (if contains text)

yvonne-c — Tue, 28 Mar 2017 15:03:00 GMT

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

Re: Expression for sum (if contains text)

ahaahaaha — Tue, 28 Mar 2017 15:15:05 GMT

Hi Yvonne,

Try

Dimension:

JobID

Expression:

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

Result

Regards,

Andrey

Re: Expression for sum (if contains text)

yvonne-c — Tue, 28 Mar 2017 15:49:04 GMT

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)

Re: Expression for sum (if contains text)

annafuksa1 — Tue, 28 Mar 2017 15:58:00 GMT

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

Re: Expression for sum (if contains text)

Kushal_Chawda — Tue, 28 Mar 2017 17:13:26 GMT

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)

Re: Expression for sum (if contains text)

muthukumar77 — Wed, 29 Mar 2017 05:38:58 GMT

Refer this app,

Re: Expression for sum (if contains text)

Anonymous — Wed, 29 Mar 2017 05:56:51 GMT

Hi ,

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

Exp

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

Thanks

Paridhi

Re: Expression for sum (if contains text)

Anonymous — Wed, 29 Mar 2017 05:58:03 GMT

I have attached qvw also

Re: Expression for sum (if contains text)

Anonymous — Thu, 30 Mar 2017 11:33:38 GMT

PFB-

LOAD CodArt,

max(Date(FromDate)) as FromDate,

min(Date(ToDate) ) as ToDate,

Price

FROM

(ooxml, embedded labels, table is Hoja1)

Group By CodArt , Price;