Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Next
bookmarks = ActiveDocument.GetDocBookmarkNames
for i = lbound(bookmarks) to ubound(bookmarks)
ExpressionString = "Count(DISTINCT {" & bookmarks(i) & "} sales)"
chart.AddExpression ExpressionString
next
end sub
The only thing to notice is that when used on a server, GetDocBookmarkNames should become GetUserBookmarkNames or GetServerBookmarkNames.
Thank you.
Ioannis,
set analysis is evaluated once per chart, not per dimension value.
Please check a possible solution / workaround here:
"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.
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
Next
bookmarks = ActiveDocument.GetDocBookmarkNames
for i = lbound(bookmarks) to ubound(bookmarks)
ExpressionString = "Count(DISTINCT {" & bookmarks(i) & "} sales)"
chart.AddExpression ExpressionString
next
end sub
The only thing to notice is that when used on a server, GetDocBookmarkNames should become GetUserBookmarkNames or GetServerBookmarkNames.
Thank you.