topic Re: Aggregation problem using variable in QlikView

Aggregation problem using variable

pennetzdorfer — Wed, 22 Aug 2012 14:41:08 GMT

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!

Re: Aggregation problem using variable

Wed, 22 Aug 2012 16:32:56 GMT

Florain:

Can you try using:

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

Kiran.

Re: Aggregation problem using variable

pennetzdorfer — Thu, 23 Aug 2012 07:11:03 GMT

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 ...

Re: Aggregation problem using variable

jonathandienst — Thu, 23 Aug 2012 08:58:10 GMT

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

Re: Aggregation problem using variable

pennetzdorfer — Thu, 23 Aug 2012 09:43:22 GMT

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

Re: Aggregation problem using variable

Thu, 23 Aug 2012 09:49:11 GMT

Hi Florain,

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

Kiran.

Re: Aggregation problem using variable

jonathandienst — Thu, 23 Aug 2012 14:02:44 GMT

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

Re: Aggregation problem using variable

pennetzdorfer — Fri, 24 Aug 2012 09:03:00 GMT

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

Re: Aggregation problem using variable

jonathandienst — Fri, 24 Aug 2012 09:14:06 GMT

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

Re: Aggregation problem using variable

jonathandienst — Fri, 24 Aug 2012 09:16:59 GMT

Possibly better, add Year to the Aggr dimensions:

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

and leave vSalesInnovation unchanged. That also works.

Regards

Jonathan

Re: Aggregation problem using variable

Fri, 24 Aug 2012 09:18:31 GMT

Is this what you are looking for?

Kiran.

Re: Aggregation problem using variable

pennetzdorfer — Fri, 24 Aug 2012 09:36:08 GMT

Kiran,

that's exactly what I need ... THANKS a lot!

That's a handy way of re-using an existing variable for a specific case with small modifications ... Great

Regards,

Florian