topic using a field name as a bookmark name in QlikView

using a field name as a bookmark name

giakoum — Fri, 21 Sep 2012 13:56:03 GMT

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 — Fri, 21 Sep 2012 14:10:10 GMT

Ioannis,

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

Please check a possible solution / workaround here:

giakoum — Fri, 21 Sep 2012 14:27:03 GMT

"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 — Fri, 21 Sep 2012 15:11:29 GMT

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

bookmarks = ActiveDocument.GetDocBookmarkNames

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

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

chart.AddExpression ExpressionString

end sub

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

Thank you.