Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
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
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?
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
Hi,
Remove the equal sign '=' while assigning the expression to the variable.
HTh
Sushil
Hi Sushil,
If I remove the '=' sign the expression returns 'sum( if (...' instead of a numerical value.
Any other ideas?
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?
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
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:
Each single row is still the total for that column.
Am I doing something wrong?
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)