3 Replies Latest reply: Sep 30, 2014 11:11 PM by Jonathan Poole RSS

    Offset addition (Straight table)

      Hi I have below requirement

      Current View

      RoleSourcesTotal
      Role1SRC1100
      SRC2123
      Role2SRC3456
      SRC4-546
      Role2SRC5124
      SRC6-456
      SRC7-132

      Please find below offset logic whihc needs to added to totals above

      Offset Logic

      SourceOffset
      SRC6SRC2
      SRC7SRC2
      SRC4SRC3

       

      Final View should be as below

      RoleSourcesTotal
      Role1SRC1100
      SRC2711
      Role2SRC31002
      SRC4-546
      Role2SRC5124
      SRC6-456
      SRC7-132

      SRC2=123-(-456)-(-132)

      SRC3=456-(-546)

      Please suggest how to implement this at UI level or Script level

       

      Thnaks in advance

        • Re: Offset addition (Straight table)
          Jonathan Poole

          I used this script to create a lookup table that you would manually populate and adjust for each offset.

           

          The offset contains the multiplier '-1'  and maps in the offsets. The chart expression is just:

           

          sum( [Total] * multiplier)

           

          Capture.PNG.png

           

           

          Sources:

          LOAD Role,

              Sources,

              Total

          FROM

          [C:\Temp\offset.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          //map all sources to themselves

          MappingSources:

          Load

            Sources,

            Sources as MappingSource,

            1 as Multiplier

          Resident Sources;

           

          //Add additional offsets manually

          Concatenate (MappingSources)

          LOAD * INLINE [

              Sources, MappingSource, Multiplier

              SR6, SR2, -1

              SR7, SR2, -1

              SR8, SR3, -1

          ];