4 Replies Latest reply: Nov 17, 2017 4:55 AM by William Choo RSS

    Sum AGGR across multiple fields with Set Expression

    William Choo

      Hi,

       

      I have a simple table as below:

      Salesman Product Cost Month
      RichardBus500JAN
      JohanCar200JAN
      JohanBus700JAN
      LeroyBoat800JAN
      LeroyPlane15000FEB
      SamuelCar300FEB
      RichardCar2000MAR
      SamuelPlane10000MAR
      LeroyCar160APR

       

      I'm trying to calc the Cost per Salesman per Month, and filter out only Salesman in a Month with > 800 Cost

      In my chart, I'm using

       

      Dimensions:

      Salesman

      Month

       

      Expressions:

      =sum( aggr( sum({<Cost={'>= $(vAmtThreshold)'}>}Cost),Salesman,Month))

       

      where vAmtThreshold = 800

       

      However, my results show up as

      Month Salesman =sum( aggr( sum({<Cost={'>= 800'}>}Cost),Salesman,Month) )
      JANLeroy800
      FEBLeroy15000
      MARRichard2000
      MARSamuel10000

       

      I am expecting ...

      JANLeroy800
      JANJohan900
      FEBLeroy15000
      MARRichard2000
      MARSamuel10000

       

      So Johan / 900 / JAN is missing from my data set ... How can I add the total Costs across Salesman/Month in this case?

      I've tried putting the AGGR as a Calc dimension and as an Expression... Both are still achieving the same output

      Is there something I'm missing?

       

      Thanks.