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

Sum when length of evaluated variable = 0

I'm trying the following formula to create a new variable for a KPI ,but it's not evaluating properly and I'm getting an invalid result:

sum(
{$<
Len($(vCrew))=0
>}
ShippedUnits
)

I can put Len($(vCrew)) in a chart measure and it works, but I just can't get it to evaluate as part of a set expression.  Please advise.  Thanks.

Labels (2)
1 Solution

Accepted Solutions
dwforest
Specialist II
Specialist II

Set analysis does not do a row by row evaluation.

Easiest way is to use the logic in load script and add a column for CrewShippedUnits

Could also use If

Sum(
If(Len($(vCrew))=0,ShippedUnits)

)

View solution in original post

3 Replies
dwforest
Specialist II
Specialist II

Set analysis does not do a row by row evaluation.

Easiest way is to use the logic in load script and add a column for CrewShippedUnits

Could also use If

Sum(
If(Len($(vCrew))=0,ShippedUnits)

)

Lauri
Specialist
Specialist

Your expression has two problems:

1. You can't use a function like that; you must use a field

2. You have to enclose the value 0 in {}

To do what you want, the simplest method is to calculate the Length during the load and then use that as your field:

sum({<FieldLength={0}>} ShippedUnits)

To do it using vCrew, you need to have a field that is a unique identifier in the table that vCrew is in, and then write the expression like:

sum({<UniqueID={"=Len([$(vCrew)])=0"}>} ShippedUnits)

I put $(vCrew) inside square brackets, assuming that vCrew evaluates to a field name without quotes or brackets.

Warning: You may need to enclose Len([$(vCrew)]) inside another $(). I don't have access to Qlik at the moment...

mikegrattan
Creator III
Creator III
Author

Thank you, the If statement was all I needed.