Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
n1ef5ng1
Creator
Creator

Simplify the code if possible

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)
&'%'
)
))


2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
marcus_sommer

Additional to the suggestion from jonathan dienst (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