topic Re: bookmark as dimension in QlikView

bookmark as dimension

gerhardl — Fri, 21 Sep 2012 08:22:09 GMT

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

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 08:34:30 GMT

count(distinct {BM01} [FieldX])

or maybe

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

Hope this helps!

Re: bookmark as dimension

gerhardl — Fri, 21 Sep 2012 08:41:03 GMT

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.

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 09:03:51 GMT

It works for me as an expression. See attached.

Re: bookmark as dimension

gerhardl — Fri, 21 Sep 2012 09:33:11 GMT

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?

Re: bookmark as dimension

gerhardl — Fri, 21 Sep 2012 09:36:03 GMT

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.

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 10:26:10 GMT

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

Re: bookmark as dimension

gerhardl — Fri, 21 Sep 2012 10:30:13 GMT

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

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 10:36:10 GMT

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?

Re: bookmark as dimension

gerhardl — Fri, 21 Sep 2012 10:39:06 GMT

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.

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 11:56:18 GMT

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.

Re: bookmark as dimension

giakoum — Fri, 21 Sep 2012 15:14:51 GMT

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.

Re: bookmark as dimension

gerhardl — Tue, 25 Sep 2012 07:02:29 GMT

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.

Re: bookmark as dimension

giakoum — Tue, 25 Sep 2012 07:55:58 GMT

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

bookmarks = ActiveDocument.GetDocBookmarkNames

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

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

chart.AddExpression ExpressionString

set chart = nothing

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

Re: bookmark as dimension

gerhardl — Tue, 25 Sep 2012 08:12:50 GMT

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).

Re: bookmark as dimension

gerhardl — Tue, 25 Sep 2012 08:26:23 GMT

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!

Re: bookmark as dimension

giakoum — Tue, 25 Sep 2012 11:33:07 GMT

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

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

Set chart = nothing

Set chartProperties= nothing

Set chartExpressions= nothing

Set bookmarks = nothing

end sub

Re: bookmark as dimension

gerhardl — Tue, 25 Sep 2012 11:50:25 GMT

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.