AGGR function using list of dimension built in a variable

Sun, 28 Sep 2014 03:06:11 GMT

Hello,

I try to create an expression using the AGGR function. The dimensions for AGGR function, however, need to be flexible.

I have created two variables:

vExclDim loads dimensions to be excluded '['&Concat(FieldsExcl,']=,[')&']= '

vDimList loads dimensions list as following '['&Concat(Fields,'],[')&'] '

The following function works as supposed:

AGGR(nodistinct count({$<Material=,[Material Type]=>} ID) , [Month] )

I would like to use vExclDim and vDimList to replace the dimension names above with the string loaded by variables:

aggr(nodistinct count({<$(vExclDim)>} ID), [Month]) ----> this works

aggr(nodistinct count({<"=$(vExclDim)">} ID), [Month]) -----> works

aggr(nodistinct count({<"=$(vExclDim)">} ID), $(vDimList)) -----> DOESN'T work. The cells display a hyphen '-'.

aggr(nodistinct count({<"=$(vExclDim)">} ID), '$(vDimList)') -----> DOESN'T work. The cells display a hyphen '-'.

What is where I am not doing right?

Thank you for your help,

Maria

Re: AGGR function using list of dimension built in a variable

Tue, 30 Sep 2014 03:45:27 GMT

Here is the solution I found.

vDimList ='['&Concat(Fields,'],[')&'] '

It loads the checked fields' names from a listbox.

Equals sign has to be typed in variable's definition. vDimList variable will load the string of dimensions.

E.g. [Month], [Partner], [Region]

Then, the aggregation formula

aggr(nodistinct count({<$(vExclDim)>} ID), $(vDimList))

will just work replace the variables with the strings previously loaded.

Regards,

Maria