17 Replies Latest reply: Sep 25, 2012 7:54 AM by Gerhard Laubscher

# bookmark as dimension

Hi there,

Is there any way I can count the number of [FieldX] that "matches" a bookmark? i.e. if I selected the bookmark, how many Accounts (my FieldX) would be "possible valaues".

Ideally I want to have a straight table with Bookmarks as my dimension and count([Account no]) as my expression.

Thanks,

Gerhard

• ###### Re: bookmark as dimension

count(distinct {BM01} [FieldX])

or maybe

count( {1<[FieldX]= P({BM01} [FieldX])>} [FieldX])

Hope this helps!

• ###### Re: bookmark as dimension

Hi,

Not sure if you mean that as my expression or calculated dimension? but either way, it doesn't seem to work. See image attached.

That also specifies a bookmark - it would have to use something like 'BM*'

Also tried the second suggestion but it doesn't seem to work.

• ###### Re: bookmark as dimension

It works for me as an expression. See attached.

• ###### Re: bookmark as dimension

I'm afraid I can't open your file (QV Personal Edition).

Which of the two expressions did you use?

Is everything correct here?

count( {1<[Account no]= P({BM05} [Account no)>} [Account no])

So I will then have to have a separate expression per Bookmark right?

• ###### Re: bookmark as dimension

Oh sorry - it does work! I had BM05 and there isn't such a bookmark.

But is there no way to use as dimension? I have SOOOOOOO many bookmarks, don't want individual expression for each.

• ###### Re: bookmark as dimension

use the counts as a dimension or the account no the bookmark returns?

• ###### Re: bookmark as dimension

Apologies - but I don't understand what you mean.

• ###### Re: bookmark as dimension

we used count as a function which only returns one number for each bookmark. All those numbers should now be a dimension, or the [account no] these numbers counted?

• ###### Re: bookmark as dimension

Let's say I have 2 bookmarks:

BM01: Currently it selects a possible 100 accounts.

BM02: Currently it selects a possible 25,543 accounts.

Some of these "possble accounts" might apply to more than once bookmark.

• ###### Re: bookmark as dimension

ok after reading carefully I understand what you need. Dont know if this can be done but let me try. Just give me some time.

Thank you.

• ###### Re: bookmark as dimension

Could not do it as a dimension but created a macro that gets the job done, I hope.

You can add this macro as a document on open action and it will create all expressions needed based on available bookmarks when the user first opens the document.

Please see attached. Hope this helps!

Ioannis.

• ###### Re: bookmark as dimension

Hi Ioannis,

Thanks a ton, but, as I mentioned, I unfortunately cannot open .qvw files.

Could you maybe just send me the macro in a text file and I'll copy and paste?

G

• ###### Re: bookmark as dimension

sorry for that!

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)"

next

set chart = nothing

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

• ###### Re: bookmark as dimension

That works great! Just some small changes I would like to make:

See image 1(bookmark - duplicated). The very first bookmark shows twice for some reason.

Image 2 (bookmark - long name). You'll see that same of the bookmarks return NULL. These bookmarks all have really long names, and when I duplicate the bookmark and just rename it to "TEST", then it counts it. Is there a limit on the length of the bookmark name for this macro to work?

Finally, is there any way to give my expressions the name of the macro?

So instead of the label being <use expression>, it should be equal to the bookmark name.

I'd also like all the expressions' Total Mode to be set to "No Totals" but this is not important (just a bonus).

• ###### Re: bookmark as dimension

Okay I figured out the "long name" issue. The problem was the spaces. I just changed to:

ExpressionString = "Count(DISTINCT {'" & bookmarks(i) & "'} [Account no])"

Still need help with the rest though, please.

Thanks!

• ###### Re: bookmark as dimension

setting the totals mode is not possible :

http://community.qlik.com/ideas/1587

Everything else should be covered by the following code :

sub createExpressions

set chart = ActiveDocument.GetSheetObject( "CH01" )

Set chartProperties= chart.GetProperties

Set chartExpressions= chartProperties.Expressions

For i=0 to chartExpressions.Count

chart.RemoveExpression 0

Next

bookmarks = ActiveDocument.GetDocBookmarkNames

j = 0

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

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

set cp = chart.GetProperties

set expr = cp.Expressions.Item(j).Item(0).Data.ExpressionVisual

expr.Label.v = bookmarks(i)

chart.SetProperties cp

j = j + 1

Set cp = nothing

Set expr = nothing

next

Set chart = nothing

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

• ###### Re: bookmark as dimension

Perfect - thank you so much!

If you feel like another challenge, please have a look at the other question I posted, which is related to this.