Test for omitted field in expression

Wed, 23 Mar 2011 10:42:57 GMT

Hello,

I have certain users that are not allowed to see a field from my fact table. I have used the OMIT field in section access to omit this field from the fact when the user opens the QlikView document. This works excellent.

My problem is in a chart that I am showing. One of the expressions in the chart uses the omitted field. Even when the field is omitted it still shows the measure in the legend. I want to get rid of the measure in the legend when the field is not present in the fact.

Let's assume that the measure that can be omitted is named [My measure]. The chart shows the sum of that measure: SUM([My measure]). Right now I use the following expression to hide the chart-expression when it is not present in the fact:

=If(Not IsNull(Min([My measure])), SUM([My measure])))

This will hide the measure from the chart when [My measure] is omitted, but it will also hide the measure when it has no data. Furthermore I don't really feel that this is the right way to do this.

I have looked at the function FieldNumber, but this only works when you are in script.

Does anyone know a way to check for the existence of a field in an expression? Or is there another way to solve the problem of omitting measures?

Thanks

Test for omitted field in expression

Fri, 25 Mar 2011 12:09:19 GMT

I have found the solution myself. The following expression works and is a lot cleaner:

=if(index(concat($Field, '|'), 'My measure') > 0, SUM([My measure]))

topic Test for omitted field in expression in QlikView

Test for omitted field in expression

Test for omitted field in expression