Sum when length of evaluated variable = 0

[mikegrattan]

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.

[dwforest · Accepted solution]

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]

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]

Thank you, the If statement was all I needed.