5 Replies Latest reply: Oct 14, 2015 10:50 AM by Dafnis X RSS

    How to use Aggr()  with various conditions

    Dafnis X



      I would like to have a straight table with one dimension:  Country, which shows various totals of the country's branches.

      Some of the calculations involve checking conditions based on branch definitions.

      But I'm not sure if I can do it by using the Aggr() function. From what I tried so far, nothing came out..

      A tweaked example:  all the fields marked in blue are defined for a branch:


      Calculating total number of employees, which is based on some conditions:

      if(SomeFlag=1 and isnull(SomeDate)=0,
             if(StockFlag=1 and SmallBranch = 0,
      sum({<YearSale=,MonthNameSale =>} aggr(max(BranchNoOFEmployees), BranchName)*vNumOfXXX)),

         sum ({<YearSale =,MonthNameSale =>} aggr(max(BranchNoOFEmployees), BranchName)*vNumOfYYY)))

      There might be some syntax errors, but i'm more concerned about the logic to be applied.