Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yvonne-c
Creator
Creator

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

 

1 Solution

Accepted Solutions
annafuksa1
Creator III
Creator III

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

View solution in original post

10 Replies
Kushal_Chawda

Create a chart

Dimension - Job ID

Expression-

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

yvonne-c
Creator
Creator
Author

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

Hi Yvonne,

Try

Dimension:

JobID

Expression:

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

Result

1.jpg

Regards,

Andrey

yvonne-c
Creator
Creator
Author

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

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
Partner - Creator III
Partner - Creator III

Refer this app,

Muthukumar Pandiyan
Anonymous
Not applicable

Hi ,

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

Exp

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

Thanks

Paridhi

Anonymous
Not applicable

I have attached qvw also