Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
n1ef5ng1
Creator
Creator
‎2015-03-11 12:10 AM

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


743 Views
0 Likes
2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III
‎2015-03-11 01:42 AM

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
387 Views
marcus_sommer
MVP
MVP
‎2015-03-11 07:10 AM

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

387 Views
0 Likes