Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

 

   

JobIDCodePriceJob Cost
42066CPMT119.2
42066V12140.2
42067BOVT230.4
42067VR15245.4
42069CPMT119.2
42069V12140.2
42074TIM211.2
42074SYRI010.25
42074VR15
42074CDA172.8
42074OPMAT225
42074SYRI010.25
42074SYRI021.1425.6
42081TIM201.6
42081VR15216.6

 

Tags (2)
1 Solution

Accepted Solutions
annafuksa1
Contributor III

Re: Expression for sum (if contains text)

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

View solution in original post

10 Replies

Re: Expression for sum (if contains text)

Create a chart

Dimension - Job ID

Expression-

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

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

ahaahaaha
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

1.jpg

Regards,

Andrey

yvonne-c
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)

annafuksa1
Contributor III

Re: Expression for sum (if contains text)

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

View solution in original post

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
Partner

Re: Expression for sum (if contains text)

Refer this app,

Muthukumar Pandiyan
paridhimantri
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

paridhimantri
Contributor

Re: Expression for sum (if contains text)

I have attached qvw also