Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

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

17 Replies
giakoum
Partner - Master II
Partner - Master II

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.

giakoum
Partner - Master II
Partner - Master II

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.

gerhardl
Creator II
Creator II
Author

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?

I will run the macro after reloading the document daily.

G

giakoum
Partner - Master II
Partner - Master II

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

          chart.AddExpression ExpressionString

next

set chart = nothing

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

gerhardl
Creator II
Creator II
Author

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

gerhardl
Creator II
Creator II
Author

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!

giakoum
Partner - Master II
Partner - Master II

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

          chart.AddExpression ExpressionString

          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

gerhardl
Creator II
Creator II
Author

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.