4 Replies Latest reply: Nov 14, 2014 12:02 PM by Tomás Lima RSS

    Using total with column name instead of fields

      Hi everybody,

      I'm trying to calculate some different results on the same graph: There's a category of articles (suppose 1 for simplicity), a set of articles belonging to that category and a set of agents that have to sell them in order to reach a target (global and personal).

      Type of  Articles, Agent and SubType are dimensions, Sales and Target fields, all other columns are calculated: I've attached the file with the table.


      Type of  ArticlesAgentSubTypeSum(Sales)Sum(Target) DeltaTOT SALESTOT TARGETTOT DELTANegative DeltaTotal Negative  DeltaFineTotal Fine
      Type  1Agent ASub11034-24146211-65-65-70-22,29-117,21
      Agent ASub2306241457075000,00-117,21
      Agent ASub332032288376167209000,00-117,21
      Agent ASub4378-75102191-89-89-99-67,42-117,21
      Agent ASub52345-2275115-40-40-32-27,50-117,21
      Agent BSub15399-46146211-65-65-41-72,93-247,72
      Agent BSub25731261457075000,00-247,72
      Agent BSub32456-32376167209000,00-247,72
      Agent BSub45478-24102191-89-89-14-152,57-247,72
      Agent BSub52535-1075115-40-40-18-22,22-247,72
      Agent CSub183785146211-65-655-65,00-194,00
      Agent CSub25833251457075000,00-194,00
      Agent CSub33279-47376167209000,00-194,00
      Agent CSub4453510102191-89-8910-89,00-194,00
      Agent CSub52735-875115-40-40-8-40,00-194,00


      At the end of each period if the global article target is reached (the sum of single agent target), all right, otherwise the global delta has to be shared between the agent that has not reached the personal aim. In this way, if the single agent delta for aricle A  is -10, 5 and -20, the first and the third have to share -25 according to 33% and 66%.


      Everything works fine except the red column (total negative delta), that is creating some problems: it has to be the sum of (only) negative delta for each article, regardless of agent.


      - sum( total <SubType> [Delta]) doesn't work because Delta is not a field but a column name. I've tried with $ expansion but without success...

      - rangesum doesn't support 'total'


      Any idea?


      Thanks in advance