Aggregation problem using variable

[pennetzdorfer]

Hey,

I'm using a variable vCurrentSales in several charts:

sum({<Year={$(vCurrentYear)} >} $(vSales))

Now, for one specific chart I would like to add another set analysis condition.

sum({<Month = {1} >} $(vCurrentSales))

The result of this expression is just NULL ... Does anybody have an idea?

Thanks in advance!

[#2]

Florain:

Can you try using:

$(Replace(vCurrentSales,'>}',',Month = {1}>}'))

Kiran.

[pennetzdorfer]

Kiran,

thank you very much! I like your suggestion, but for some reason the formula still shows NULLs.

When I omit the Dollar-function $(...) I get the following string:

 sum({<Year={$(vCurrentYear)}, Month = {1}>} $(vSales))

which seems okay actually.

There must be a litte thing that I miss ...

[jonathandienst]

Florian

What is in vSales? Have you tested that

Sum($(vSales))

works.This is just to test that vSales is not the problem

Regards

Jonathan

[pennetzdorfer]

Hi Jonathan,

I've just tested it and you're right: Sum($(vSales)) doesn't work either.

Variable vSales contains:

sum({< FlagInnovation = {1} >} Sales)

I can't see the problem ...

Regard,

Florian

[#6]

Hi Florain,

Can you post a sample of your application with just relavant data and variables created?

Kiran.

[jonathandienst]

Florian

You cant nest aggregation functions. Sum(Sum(....)) is not allowed. First see if you can do this another way, but if you must nest them, you will need to use Aggr().

Sum(Aggr(Sum({< FlagInnovation = {1} >} Sales), some dimension(s)))

The dimensions must be a comma separated list of one or more field names for which the inner sum is being evaluated, and must include the chart dimensions if inside a chart.

Regards

Jonathan

[pennetzdorfer]

Hi Kiran/Jonathan,

thank you very much for your help!

Nested aggregation seems to be the problem. I've attached a sample file now.

To simplify matters I modified my example a little bit (other variables) - but I think, it still illustrates my problem.

Regards,

Florian

[jonathandienst]

Florian

The set analysis does not apply into the aggregation function, so you need to re-apply the expression inside the aggregation. In your example, I think you get the correct results if you change vSalesInnovation to:

sum({<FlagInnovation = {1}, Year = {'2012'}>} Sales)

in fact, you can remove the Year condition in the chart expression as it is redundant, so:

sum(aggr($(vSalesInnovation),Country))

Hope that helps

Jonathan

[jonathandienst]

Possibly better, add Year to the Aggr dimensions:

sum({<Year = {"2012"}>}aggr($(vSalesInnovation),Country, Year))

and leave vSalesInnovation unchanged. That also works.

Regards

Jonathan