Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gerhardl
Contributor 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

1 Solution

Accepted Solutions
giakoum
Honored Contributor II

Re: bookmark as dimension

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

17 Replies
giakoum
Honored Contributor II

Re: bookmark as dimension

count(distinct {BM01} [FieldX])

or maybe

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

Hope this helps!

gerhardl
Contributor II

Re: bookmark as dimension

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*'

Count BM.jpg

Also tried the second suggestion but it doesn't seem to work.

giakoum
Honored Contributor II

Re: bookmark as dimension

It works for me as an expression. See attached.

gerhardl
Contributor II

Re: bookmark as dimension

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
Contributor II

Re: bookmark as dimension

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
Honored Contributor II

Re: bookmark as dimension

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

gerhardl
Contributor II

Re: bookmark as dimension

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

giakoum
Honored Contributor II

Re: bookmark as dimension

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
Contributor II

Re: bookmark as dimension

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.

Community Browser