Solved: bookmark as dimension - Qlik Community

gerhardl · ‎2012-09-21

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

giakoum · ‎2012-09-25

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

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

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

chart.AddExpression ExpressionString

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

View solution in original post

giakoum · ‎2012-09-21

count(distinct {BM01} [FieldX])

or maybe

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

Hope this helps!

gerhardl · ‎2012-09-21

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.

giakoum · ‎2012-09-21

It works for me as an expression. See attached.

gerhardl · ‎2012-09-21

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?

gerhardl · ‎2012-09-21

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.

giakoum · ‎2012-09-21

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

gerhardl · ‎2012-09-21

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

giakoum · ‎2012-09-21

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?

gerhardl · ‎2012-09-21

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.