topic Bookmark in set analysis from variable in QlikView

Bookmark in set analysis from variable

Tue, 10 Jun 2014 12:49:47 GMT

Ok, so I have a expression like so

=Sum({$<

[Organisational Unit]=P({BK1}[Organisational Unit])

>} [measure])

This works great.

Now what I want to do is to pick up the bookmark name from a variable so I can use this in a chart table with a calculated dimension that pulls the bookmark names.

I have tried everything I can think of to no avail, could someone help point me in the right direction?

Thanks

Message was edited by: Chris Cooil

Re: Bookmark in set analysis from variable

whiteline — Tue, 10 Jun 2014 13:13:34 GMT

Hi.

Have you tried something like this ?

=Sum({$<[Organisational Unit]=P($(vBookMark)[Organisational Unit])>} [measure])

vBookMark='{BK1}'

Re: Bookmark in set analysis from variable

Tue, 10 Jun 2014 13:29:36 GMT

Thanks! Ok so yes that works...................but the problem is that the vBookMark looks like this

='{' & subfield(varBookmarks),chr(167),ValueSequence) & '}'

This is turning a string of bookmarks into a dimension using ValueSequence (1,2,3,4,5.....)

This is the dimension in the table that the expression should return values for.

Re: Bookmark in set analysis from variable

whiteline — Tue, 10 Jun 2014 14:36:57 GMT

Set analysis is applyed only once per entire chart.

So you can't use it to evaluate the expression on diferent datasets depending on the dimension value.

You have to rethink about your the result that you trying to achieve.

Re: Bookmark in set analysis from variable

Tue, 10 Jun 2014 15:12:18 GMT

Ok thanks. So I've loaded a field that gives me the bookmarks names :BklField, created a variable that produces the line in the expression

'[Organisational Unit]=P({' & BkField & '}[Organisational Unit])'

Now when I call this variable in the expression no data is returned but if I just call the variable in the expression it return the correct string as below

Re: Re: Bookmark in set analysis from variable

IAMDV — Tue, 10 Jun 2014 15:19:09 GMT

Chris - Have you tried using dollar sign expansion? Something like this?

=$(='[Organisational Unit]=P({' & BkField & '}[Organisational Unit])')

Cheers,

Re: Re: Bookmark in set analysis from variable

Tue, 10 Jun 2014 15:27:14 GMT

Thanks Deepak, it works if I hardcode the bookmark name but not if I sue the field name

this is fine:

=Sum({$<

$(='[Organisational Unit]=P({' & 'BSPAIN' & '}[Organisational Unit])')

>} [FTE])

this is not:

=Sum({$<

$(='[Organisational Unit]=P({' & BkField & '}[Organisational Unit])')

>} [FTE])

Thanks for your help though!

Re: Re: Bookmark in set analysis from variable

IAMDV — Tue, 10 Jun 2014 15:37:58 GMT

Chris - Do you mind posting the sample app? It's easier to work on your example. I'm sure we can work out a way.

Cheers,

www.QlikShare.com

Re: Re: Bookmark in set analysis from variable

Tue, 10 Jun 2014 16:04:32 GMT

It's a big app so let me try and breakout the relevant parts. Thanks

Re: Re: Bookmark in set analysis from variable

whiteline — Tue, 10 Jun 2014 19:51:31 GMT

The same reason.

Expansion is processed once and then the resulting expression is used for each dimension value.

Re: Bookmark in set analysis from variable

whiteline — Tue, 10 Jun 2014 20:27:47 GMT

How much bookmarks do you want to precess ?

Re: Bookmark in set analysis from variable

Wed, 11 Jun 2014 14:11:45 GMT

The number of bookmarks is dynamic.

Re: Bookmark in set analysis from variable

Wed, 11 Jun 2014 16:13:38 GMT

Sample app uploaded

Re: Re: Bookmark in set analysis from variable

IAMDV — Thu, 12 Jun 2014 13:17:51 GMT

Chris - Quick question. Do you want to Sum(FTE) for each bookmark. Is this right? I wanted to fully understand your requirements before attempting to solve this one.

Thanks,

Re: Re: Bookmark in set analysis from variable

Thu, 12 Jun 2014 13:20:11 GMT

That's right

Re: Re: Re: Bookmark in set analysis from variable

IAMDV — Thu, 12 Jun 2014 15:48:25 GMT

Hi Chris,

I've got it working with 2 variables and 2 expressions. See the attachment and let me know if this is what you wanted.

I can explain in-detail if you don't follow it completely. I've tested by adding new bookmark and it works fine.

Cheers,

www.QlikShare.com

Re: Bookmark in set analysis from variable

Fri, 13 Jun 2014 12:53:19 GMT

This works well, thanks so much.

I've tweaked it a bit so that that only the orgunit field is using the bookmarks (using P)

but this approach was an innovative way of doing it.

Thanks again!

Re: Bookmark in set analysis from variable

Fri, 13 Jun 2014 14:59:52 GMT

Thanks for all the help guys!

Re: Re: Bookmark in set analysis from variable

IAMDV — Fri, 13 Jun 2014 16:00:09 GMT

You're welcome mate. It was interesting problem to solve and I love these kind of challenges.

Cheers,

www.QlikShare.com

Re: Re: Bookmark in set analysis from variable

Tue, 17 Jun 2014 09:08:21 GMT

Thanks again.