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

    Filter in Expression with Variable based on Bookmark

    Maarten Borsten

      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