using a field name as a bookmark name

[giakoum]

Hi all.

I have the following expression which works as expected :

Count(DISTINCT {BM01} personid) where BM01 is a bookmark id.

What I need to do is replace BM01 with a field i.e. Bookmarks in which I have loaded all bookmarks IDs. So my expression should look like that :

Count(DISTINCT {[Bookmarks]} personid)

so that if I add this expression in a chart with Bookmarks field as a dimension, I would get one value per bookmark.

The above does not work. Any suggestions?

Thank you

Ioannis.

[swuehl]

Ioannis,

set analysis is evaluated once per chart, not per dimension value.

Please check a possible solution / workaround here:

http://community.qlik.com/docs/DOC-1335

[giakoum]

"set analysis is evaluated once per chart, not per dimension value."

yes, that is known, however I was hopping for a workaround. not sure if the one you propose will help, but i am currently checking it.

Thank you.

[giakoum · Accepted solution]

I use a macro that creates all the expressions needed based on the existing bookmarks :

sub createExpressions

Set chart = ActiveDocument.GetSheetObject( "CH01" )

Set chartProperties= chart.GetProperties

Set chartExpressions= chartProperties.Expressions

For i=0 to chartExpressions.Count

          chart.RemoveExpression 1

Next

bookmarks = ActiveDocument.GetDocBookmarkNames

for i = lbound(bookmarks) to ubound(bookmarks)

          ExpressionString = "Count(DISTINCT {" & bookmarks(i) & "} sales)"

          chart.AddExpression ExpressionString

next

end sub

The only thing to notice is that when used on a server, GetDocBookmarkNames should become GetUserBookmarkNames or GetServerBookmarkNames.

Thank you.