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
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
count(distinct {BM01} [FieldX])
or maybe
count( {1<[FieldX]= P({BM01} [FieldX])>} [FieldX])
Hope this helps!
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.
It works for me as an expression. See attached.
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?
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.
use the counts as a dimension or the account no the bookmark returns?
Apologies - but I don't understand what you mean.
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?
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.