Bookmark in set analysis from variable

[#1]

Ok, so I have a expression like so

=Sum({$<

[Organisational Unit]=P({BK1}[Organisational Unit])

>} [measure])

This works great.

Now what I want to do is to pick up the bookmark name from a variable so I can use this in a chart table with a calculated dimension that pulls the bookmark names.

I have tried everything I can think of to no avail, could someone help point me in the right direction?

Thanks

Message was edited by: Chris Cooil

[whiteline]

Hi.

Have you tried something like this ?

=Sum({$<[Organisational Unit]=P($(vBookMark)[Organisational Unit])>} [measure])

vBookMark='{BK1}'

[#3]

Thanks! Ok so yes that works...................but the problem is that the vBookMark looks like this

='{' & subfield(varBookmarks),chr(167),ValueSequence) & '}'

This is turning a string of bookmarks into a dimension using ValueSequence (1,2,3,4,5.....)

This is the dimension in the table that the expression should return values for.

[whiteline]

Set analysis is applyed only once per entire chart.

So you can't use it to evaluate the expression on diferent datasets depending on the dimension value.

You have to rethink about your the result that you trying to achieve.

[#5]

Ok thanks. So I've loaded a field that gives me the bookmarks names :BklField, created a variable that produces the line in the expression

'[Organisational Unit]=P({' & BkField &  '}[Organisational Unit])'

Now when I call this variable in the expression no data is returned but if I just call the variable in the expression it return the correct string as below

[IAMDV · Accepted solution]

Chris - Have you tried using dollar sign expansion? Something like this?

=$(='[Organisational Unit]=P({' & BkField &  '}[Organisational Unit])')

Cheers,

DV

[#7]

Thanks Deepak, it works if I hardcode the bookmark name but not if I sue the field name

this is fine:

=Sum({$<

$(='[Organisational Unit]=P({' & 'BSPAIN' &  '}[Organisational Unit])')

>} [FTE])

this is not:

=Sum({$<

$(='[Organisational Unit]=P({' & BkField & '}[Organisational Unit])')

>} [FTE])

Thanks for your help though!

[IAMDV]

Chris - Do you mind posting the sample app? It's easier to work on your example. I'm sure we can work out a way.

Cheers,

DV

www.QlikShare.com

[#9]

It's a big app so let me try and breakout the relevant parts. Thanks

[whiteline]

The same reason.

Expansion is processed once and then the resulting expression is used for each dimension value.