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

## Simplify the code if possible

Hi 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({
<
+
<
+
<
+
<
}
TEU)

)
/
sum({
<
+
<
+
<
+
<
}
TEU)

)

*100,0.1)
&'%'
)
,
(
Round(
(
(
sum({
<
+
<
+
<
+
<
}
TEU)
-
sum({
<
+
<
+
<
+
<
}
TEU)

)
/
sum({
<
+
<
+
<
+
<
}
TEU)
)
*100,0.1)
&'%'
)
))

2 Replies
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)

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

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

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

Community Browser