topic Re: Storing the straight table's (complex expression) column Total in a variable for later reference in QlikView

Storing the straight table's (complex expression) column Total in a variable for later reference

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I am going to ask a question very similar to this one, but I started a new discussion because the solution there does not work for me and I suspect because of the complex expression. So let me show you a simple straight table (there is only one row in this example, but there can be more):

The first two columns are dimensions. Qta is an expression based on Set Analysis for filtering based on a different field:

Sum({<Tipo_MP = {10}>} Qta_composizione)

And now the last column, here it is the definition:

Qta*$(ValorizzazioneUnitariaMP_C1(id_method, unit_value))

So the Qta column's value is multiplied by the result returned from a variable with parameters. The purpose of this variable is to perform different math according to the value of the id_method field. This variable is much like a function. Here it is its definition (just a simple example, this variable can eventually be more complex than this):

Pick(Num($1), Num($2), AVG(valore_medio))

So, you see, some pick choices may also be aggregates.

What I need next is to store in another variable the total result in the table (which is configured as sum of rows).

Up to now, any attempt to use Sum(Aggr(...)) construct on the above expression leads to result 0.

The following is my last iteration of a variable definition before asking for help:

=Sum(

Aggr(

{<Tipo_MP = {10}>} Qta_composizione*($(ValorizzazioneUnitariaMP_C1 (id_method, unit_value))),

Descrizione_MP,

)

Is there a way to achieve my purpose?

Thanks in advance for your help,

Best regards,

Re: Storing the straight table's (complex expression) column Total in a variable for later reference

Anonymous — Thu, 11 May 2017 16:15:19 GMT

Try

RangeSum in place of your Sum

Re: Storing the straight table's (complex expression) column Total in a variable for later reference

Fri, 12 May 2017 08:02:17 GMT

Hi and thanks for your answer.

Unfortunately no luck, I still get a result of 0.

Re: Storing the straight table's (complex expression) column Total in a variable for later reference

Fri, 12 May 2017 14:06:54 GMT

Just for the purpose of sharing, after some trials and errors, I eventually got a variable definition that yields the intended result:

=Sum( {<Tipo_MP = {10}>}

Aggr(

Qta_composizione*$(ValorizzazioneUnitariaMP_C1(id_method, unit_value))

, Descrizione_MP

)

Not only the structure, but there was also a syntax detail that prevented the expression from working:

[...] ValorizzazioneUnitariaMP_C1(id_method, [...]

I initially had a blank space between the variable name and the parameters paranthesis which I had to remove as in the above line.

So far so good.

Re: Storing the straight table's (complex expression) column Total in a variable for later reference

Anonymous — Fri, 12 May 2017 15:48:57 GMT

Glad it figured it out!