topic Re: Aggr Function on a Variable in QlikView

Aggr Function on a Variable

Thu, 17 Sep 2015 14:51:58 GMT

Hello QV Users;

I have a variable that is used as a expressions in various places of a model, the variable is:
vPriceVar_OnVolume =

(aggr(($(vCRevInv) / $(vCUnits)) - ($(vPRevInv) / $(vPUnits))/ $(vPRate) * ($(vCUnits)-$(vPUnits)),PartCode,InvoiceCurrencyCode2))

I want to maintain the variable as is (do not want to create a new variable), but want to use it

only in one expression of the table with an additional aggr dimension (FamilyCode) like:

(aggr(($(vCRevInv) / $(vCUnits)) - ($(vPRevInv) / $(vPUnits))/ $(vPRate) * ($(vCUnits)-$(vPUnits)),PartCode,InvoiceCurrencyCode2,FamilyCode))

How can I add the additional aggr dimension to the variable, without creating a new variable or without

using the whole formula in the expression - can an additional aggr dimension added to an existing variable ?

appreciate any help that can be extended.

thanks

Kumar

Re: Aggr Function on a Variable

swuehl — Thu, 17 Sep 2015 15:58:01 GMT

I don't believe that's possible without modifying the variable.

But maybe you can modify your variable to enable dollar sign expansion with parameters, so you can use the same variable, and control the number of aggr() dimensions by the dollar sign expansion arguments.

From the HELP:

Dollar-Sign Expansion with Parameters

Parameters can be used in variable expansions. The variable must then contain formal parameters, such as $1, $2, $3 etc. When expanding the variable, the parameters should be stated in a comma separated list.

Examples:

set MUL=’$1*$2’;

set X=$(MUL(3,7)); // returns '3*7' in X

let X=$(MUL(3,7)); // returns 21 in X

If the number of formal parameters exceeds the number of actual parameters only the formal parameters corresponding to actual parameters will be expanded. If the number of actual parameters exceeds the number of formal parameters the superfluous actual parameters will be ignored.

Examples:

set MUL=’$1*$2’;

set X=$(MUL); // returns '$1*$2' in X

set X=$(MUL(10)); // returns '10*$2' in X

let X=$(MUL(5,7,8)); // returns 35 in X

The parameter $0 returns the number of parameters actually passed by a call.

Example:

set MUL='$1*$2 $0 par';

set X=$(MUL(3,7)); // returns '3*7 2 par' in X

Re: Aggr Function on a Variable

Thu, 17 Sep 2015 17:28:00 GMT

Thanks Swuehl, your feedback would solve my issue, appreciate the help

Re: Aggr Function on a Variable

Wed, 23 Sep 2015 09:22:37 GMT

Hello,

I have exactly the same problem but I don't have understood your answer.

I have quantity by reference and the cost by reference (which is a calculated vvariable). So what I want to do is to get the total cost (costXquantity) by product line which goupped several references.

If I do it with the number of my reference it works but if I want the sum of this product line, I don't get the total.

I hope I have been clear.

Thanks in advance for support