1 Reply Latest reply: May 20, 2016 9:35 AM by Maarten Borsten

# Filter in Expression with Variable based on Bookmark

Hi guys,

Maybe I am making it things unnecessary complicated, but this is what I am trying achieve.

I have a graph that calculates the number of order lines shipped per order type. The titel of the graph is

='Total Lines = '&num(Count (distinct {<[B/P] = {'BED001'}>}  [Shipment Number2]),'#,##0') &' (' & num(count(distinct {<[B/P] = {'BED001'}>} [Shipment Number2])/count(distinct {1} [Shipment Number2]),'#,##0.0%')& ')'

So in my example this looks like 'Total Lines = 499,492  (7.6%)'.

Today I was surfing the forum and found a way to filter on a Bookmark

='Total Lines = '&num(Count (distinct {<[B/P] = {'BED001'}>}  [Shipment Number2]),'#,##0') &' (' & num(count(distinct {<[B/P] = {'BED001'}>} [Shipment Number2])/count(distinct {BM58} [Shipment Number2]),'#,##0.0%')& ')'

Then my results is 'Total Lines = 499,492  (100%)'. That was what I was looking for. But this 499,492 is the result for my bookmark for January. February has bookmark BM59...

So I thought I could create a variable: vBoomark and do:

='Total Lines = '&num(Count (distinct {<[B/P] = {'BED001'}>}  [Shipment Number2]),'#,##0') &' (' & num(count(distinct {<[B/P] = {'BED001'}>} [Shipment Number2])/count(distinct {vBookmark} [Shipment Number2]),'#,##0.0%')& ')'

Where my variable is set as BM58, BM59 etc....

However vBookmark is not accepted in the formula between {} in the current set-up. Neither was =vBoomark or 'vBookmark' or any other probably stupid option I tried.

Should this be possible or is it not?

Thanks all

• ###### Re: Filter in Expression with Variable based on Bookmark

I had it working with using my selection within the date so

='Total Lines = '&num(Count (distinct {<[B/P] = {'BED001'}>}  [Shipment Number2]),'#,##0') &' (' & num(count(distinct {<[B/P] = {'BED001'}>} [Shipment Number2])/count(distinct <Date> [Shipment Number2]),'#,##0.0%')& ')'

But I am still curious if above asked is possible