Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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
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).
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!
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
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.