Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
)
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)
)
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...
Thank you, the If statement was all I needed.