2 Replies Latest reply: Mar 11, 2015 7:10 AM by Marcus Sommer RSS

    Simplify the code if possible

    ben lim

      Capture.JPGHi all,

      Below is a lengthy code that I have coded on the label of the chart as attached. It gives me the Alliance computed MOM%, YOY% and YTD% with extrapolation involved. I am thinking whether is there any ways to shorten the code and more efficient in maintaining. Thanks.

      Some explanation on the codes below :

      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'}

      means I am taking the latest month and today()-3 data... meaning today as of Mar 11, I am taking in 8 march data

        / Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))


      This means extrapolation. I am dividing the volumes divide by Day(var_enddate) which is 8 * the number of days for this month to get extrapolation vol for March

      =' Alliance
      ' & 'MOM: ' & (
      if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
      =
      MonthEnd( var_EndDate ) ,
      (
      Round(
      (
      (
        (
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
        /
      Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))
        )
      -
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
      <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)


      )

      *100,0.1)
      &'%'
      )
      ,
      (
      Round(
      (
      (
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
        -
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
      )
      *100,0.1)
      &'%'
      )
      ))






      & '   YOY: '
      &

      (
      if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
      =
      MonthEnd( var_EndDate ) ,
      (
      Round(
      (
      (
        (
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
        /
      Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))
        )
      -
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)


      )

      *100,0.1)
      &'%'
      )
      ,
      (
      Round(
      (
      (
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
        -
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
      )
      *100,0.1)
      &'%'
      )
      ))





      & '   YTD: '
      &

      (
      if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
      =
      MonthEnd( today() ) ,

      (
      Round(
      (
      (
        (
      sum({
        <
      Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

        )
      -
      sum({
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)


      )

      *100,0.1)
      &'%'
      )
      ,
      (
      Round(
      (
      (
      sum({
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
        -
      sum({
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)

      )
      /
      sum({
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
        +
        <
      Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
        }
      TEU)
      )
      *100,0.1)
      &'%'
      )
      ))


        • Re: Simplify the code if possible
          Jonathan Dienst

          Hi

           

          You need a bit more of the T* in your ETL to prepare your data for effective analysis. Take this clause for example:

           

          sum({

            <Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>

            +

            <Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>

            +

          <Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>

            +

            <Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>

            }TEU)

           

          In your load script, you can define the variable:

           

          Set vMonthMinusOne = =(max({<Month=>} Month_Id)-1);

           

          Then in your load statement, add the following code:

           

          LOAD ...

                 If(Consortium = ‘HUH’ And Match(CO_Operator_Group_Code, 'AA’,’AB’)

                 Or (Consortium = ‘MAO’ And Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Match(Service, 'A20','AE3','A11','A12','FL3'))

                 Or (Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Match(Service, ‘AA1’,’AA2’,’AA3’,’AQ4’))

                Or (Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Service = 'OTH' And Match(Vessel_Name, ‘VES1’.’CSR1’,’FEFEWF’)), 1, 0) As Flag1,

            ...

           

          Now that clause in the expression becomes:

           

          Sum({<Month_Id = {'$(=vMonthMinusOne)'}, Flag1 = {1}>} TEU)

           

          Repeat this exercise with the other clauses and date criteria....

           

          HTH

          Jonathan

           

          *T = Transform

          • Re: Simplify the code if possible
            Marcus Sommer

            Additional to the suggestion from jontydkpi (you should do it first) - if you used some expression-varities often you could put some expression-parts into variables or you could use parametrized variables as expression, here an example:

             

            //variable

            eExpression:

            sum({$1<$(=pick(match($2, 'd', 'mtd', 'm'),

                'date = {"$(=date(max(date), 'DD.MM.YYYY'))"}',

                'date = {">=$(=date(monthstart(max(date)), 'DD.MM.YYYY'))<=$(=date(max(date), 'DD.MM.YYYY'))"}',

                'date = {">=$(=date(monthstart(max(date)), 'DD.MM.YYYY'))<=$(=date(monthend(date), 'DD.MM.YYYY'))"}'))>} $3)

             

            //expression

            $(eExpression(1, 'mtd', [Sales])) or maybe $(eExpression('', 'm', [Revenue]))

             

            - Marcus