0 Replies Latest reply: Jul 15, 2014 12:25 PM by Percy Dobbelsteyn RSS

    Help with Set Expression usage in Bar Chart



      Can someone please help me with constructing a proper Bar Chart.

      I'm trying to use a set-expression, instead of using a really REALLY SLOW sum(if(...


      Here's my data:



      QUALIFY *;


      LOAD * INLINE [

      ResourcePoolName, Capacity

      Pool A, 100

      Pool B, 200

      Pool C, 300





      LOAD * INLINE [

      PoolName, Demand, Person

      Pool A, 50, Mirjam

      Pool A, 25, Percy

      Pool A, 10, Jerry

      Pool B, 175, Jerry

      Pool C, 100, Mirjam

      Pool C, 100, Percy

      Pool C, 100, Jerry




      Table2.PoolName field has a document trigger to also select the same Table1.ResourcePoolName. I have very good reasons for this, so please don't give any answers that the two fields should be named the same.


      Dimension in bar chart:



      Two expressions:

      1. Demand:  sum(Table2.Demand)

      2. Capacity: Difficulties here!!!


      I've tried:

      2. sum(if(Table2.PoolName = Table1.ResourcePoolName, Table1.Capacity, 0))   [error here because of counting doubles AND VERY VERY SLOW]

      2. sum({$<Table1.ResourcePoolName = P(Table2.PoolName)>} Table1.Capacity)  [wrong]

      2. sum({$<Table2.PoolName = P(Table1.ResourcePoolName)>} Table1.Capacity) [wrong too]

      2. sum({$<Table1.ResourcePoolName = {$(=Table2.PoolName)} >} Table1.Capacity) [wrong]

      2. sum({$<Table2.PoolName= {$(=Table1.ResourcePoolName )} >} Table1.Capacity) [wrong]


      I've heard the set expression is faster than the IF statement, but I can't get it to work.

      Any help is greatly appreciated.


      I have other reasons why these are not coupled