topic Re: Using alias for expressions in QlikView

Using alias for expressions

Anonymous — Mon, 27 May 2013 10:51:58 GMT

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!

Re: Using alias for expressions

sushil353 — Mon, 27 May 2013 10:58:25 GMT

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

Re: Using alias for expressions

Anonymous — Mon, 27 May 2013 11:18:51 GMT

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?

Re: Using alias for expressions

er_mohit — Mon, 27 May 2013 11:19:45 GMT

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

Re: Using alias for expressions

sushil353 — Mon, 27 May 2013 11:44:09 GMT

Hi,

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

HTh

Sushil

Re: Using alias for expressions

Anonymous — Mon, 27 May 2013 12:21:59 GMT

Hi Sushil,

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

Any other ideas?

Re: Using alias for expressions

Anonymous — Mon, 27 May 2013 12:23:43 GMT

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?

Re: Using alias for expressions

er_mohit — Mon, 27 May 2013 12:29:09 GMT

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

Re: Using alias for expressions

Anonymous — Mon, 27 May 2013 12:37:15 GMT

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:

Open variable window
create variable
variable name = stockprev_ma
definition = '=sum( if(component_cd = mainrawmat_cd, quantity_av_comp, 0))'
close
table property
add expression
expression = 'stockprev_ma'
OK

Each single row is still the total for that column.

Am I doing something wrong?

Re: Using alias for expressions

er_mohit — Mon, 27 May 2013 12:51:05 GMT

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)