Using alias for expressions

[Anonymous]

Hello community,

I'm creating a report which shows me the recommended production/purchases of each product that should be made according to their sales history.

The problem is that since each column utilizes the information from the previous column, the expressions become massively long.

For example, the second month uses information for the first month. The third month uses information from the second and the first month.

Is there anyway to create aliases for the expressions we create for the columns?

For example, instead of writing:

• //Stock MA
  sum( if(component_cd = mainrawmat_cd, quantity_av_comp, 0))
  +
  //Ordine MA vFineMese
  sum({$<c_doc_cd = {"OFOR"}, c_expected_del_dt= {'<=$(=Date(vFineMese))'}>} c_quantity-c_deliv_qt)
  -
  //Consumo Componente TOTALE
  (
  sum(total (aggr(if ((Sum(quantity_st)- sum({$<doc_cd = {"OCLI", "OCPI"}, doc_st = {"4", "7"}>} saldo_qt)) -
  ((Sum( {$<doc_kind={"SALES"}, mov_dt={'>=$(=Date(vOggiMesePrec6)) <=$(=Date(vOggi))'}>} quantity))/6)/30*(vFineMese-vOggi) >
  (Sum( {$<doc_kind={"SALES"}, mov_dt={'>=$(=Date(vOggiMesePrec6)) <=$(=Date(vOggi))'}>} quantity))/6, 0, min_st), item_cd))*
  anagart_comp_cons)
  )

I would write just StockPrev_MA instead of copying this expression every time I have to calculate the this value.

And this is just for the first month. I won't put the second month to keep the post tidier.

This way, I could keep the expressions simpler and not propagate the enormous expressions for each additional month I have to create.

Thank you for your help!

[sushil353]

you can use variable to store the expression value and then use them in the expressions..

other way is you can use already created expression in that particular chart.. say if you have created an expression with name Exp1 then further you can use that expression with [Exp1] name..

HTH

Sushil

[Anonymous]

Hi Sushil,

Thank you for your response, but unfortunately that didn't work.

When I created the variable and substituted it in the expression it returns the total value for that column instead of separating it for each product.

So all my rows come with the same value, which is the total value.

Do you know anyway to go around this problem?

[er_mohit]

create variable

press ctrl+alt+v

then add variablename StockPrev_MA

in defination with equal sign '=' paste your expression  then click on ok and in column write this variable StockPrev_MA

hope it helps

[sushil353]

Hi,

Remove the equal sign '=' while assigning the expression to the variable.

HTh

Sushil

[Anonymous]

Hi Sushil,

If I remove the '=' sign the expression returns 'sum( if (...' instead of a numerical value.

Any other ideas?

[Anonymous]

Hi er.mohit,

For what I understood your answer is similar to what Sushil told me to do.

Unfortunately it doesn't return the right value.

It returns the total value for that column instead of for each specific row (or product).

Any other ideas?

[er_mohit]

try to make your all three expression one by one in variable then use that variable in expression like

variable1 =sum( if(component_cd = mainrawmat_cd, quantity_av_comp, 0))

variable2=sum({$<c_doc_cd = {"OFOR"}, c_expected_del_dt= {'<=$(=Date(vFineMese))'}>} c_quantity-c_deliv_qt)

variable3=(
sum(total (aggr(if ((Sum(quantity_st)- sum({$<doc_cd = {"OCLI", "OCPI"}, doc_st = {"4", "7"}>} saldo_qt)) -
((Sum( {$<doc_kind={"SALES"}, mov_dt={'>=$(=Date(vOggiMesePrec6)) <=$(=Date(vOggi))'}>}quantity))/6)/30*(vFineMese-vOggi) >
(Sum( {$<doc_kind={"SALES"}, mov_dt={'>=$(=Date(vOggiMesePrec6)) <=$(=Date(vOggi))'}>}quantity))/6, 0, min_st), item_cd))*
anagart_comp_cons)
)

then in expresion you used one coloumn for variable1

2nd for variable2

3rd for variable3

and last one column variable1-variable2+variable3

hope it helps

[Anonymous]

When you say to create the "variable1 = sum( if(component_cd = mainrawmat_cd, quantity_av_comp, 0))"

You mean to create it in the variable window?

I did this:

1. Open variable window
2. create variable
3. variable name = stockprev_ma
4. definition = '=sum( if(component_cd = mainrawmat_cd, quantity_av_comp, 0))'
5. close
6. table property
7. add expression
8. expression = 'stockprev_ma'
9. OK

Each single row is still the total for that column.

Am I doing something wrong?

[er_mohit]

ok because in this expression it can't understand how both are component_cd = mainrawmat_cd so it gives you total for each row you have to make same name mainrawmat_cd as component_cd and then your mainrawmat_cd based upon one uniquefield use that field for comparision then try this syntax

hope it helps you

sum( {<component_cd = P({1<uniquefield={'**'}>}component_cd)>}quantity_av_comp)