topic Aggr Nested in Set Analysis in QlikView

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 17:16:56 GMT

A simple example but I am stuck ...

Previous Year

=Sum({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>}aggr(ApprovedPC, ActivityID)) is not returning any results

Selected Year

=Sum({$<ActivityStartYear={$(=Only(ActivityStartYear))}>}aggr(ApprovedPC, ActivityID)) ... is ... the difference being the -1

Basically I am trying to do a simple year on year table but need to aggregate the results by ActivityID before summing the value.

Could anyone explain why it fails on previous year but selected year is OK?

Thanks

Lee

Aggr Nested in Set Analysis

isaiah82 — Mon, 14 Feb 2011 17:24:54 GMT

Hi Lee - try sticking the expressions to determine your year into a variable and then substitute into the set analysis expression.

Let me know if this doesn't make any sense.

-Isaiah

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 17:37:55 GMT

Put your Previous Year into an expression, but don't give it a label. When the chart is rendered, the label will be the Set Analysis expression with the dollar sign expansion evaluated. I don't see an obvious reason as why the Selected Year would work, but the previous would not. You are making a selection when using the previous year expression, correct?

I would also try to put the Aggr() outside of the Sum(). I don't know if it matters in this instance though. It may be worth a try.

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 17:46:39 GMT

Hi,

@Isaiah - Using a variable for both previous and selected year has got them both calculating but the -1 is being ignore, so they are now both calculating the selected year ... it now looks like this ...

=Sum({$<vPreviousYear={$(=Only(vPreviousYear))}>}aggr(ApprovedPC, ActivityID))

@NMiller - thanks for idea ... the AGGR is pretty crucial to getting the value correct ... the selected year is correct it just won't calculate the year ...

However - I have a dynamic label on the previous year column and that is working fine ...

='Previous Year - ' & $(vPreviousYear)

So it is something to do with calculating the previous year and the AGGR ... or so it would seem ... might be off course.

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 17:58:53 GMT

I'm not sure where the Set Analysis is being calculated. When the Aggr() is on the outside, it means Sum according to this logic, while aggregating by Activity ID. When the Aggr is inside, it is Aggregating by Activity ID and then summing according to the logic.

The problem seems to come from your -1, with all else being equal. You need to make sure your dollar sign expansion is returning a value for the previous year and then look into the Aggr().

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 18:24:37 GMT

Ah ... I think the AGGR() is causing the SET to not evaluate correctly ... because hardcoding the different years in is not making a difference ...

Not too sure how to get round that ...

Aggr Nested in Set Analysis

pover — Mon, 14 Feb 2011 18:41:36 GMT

Lee,

When you do a aggr(ApprovedPC,ActivityID) it is like doing a aggr(only(ApprovedPC,ActivityID). I'm not sure why you would do that because it returns null if a ActivityID has more than 1 unique value, but maybe that is necessary in your case, so try the following:

Sum(aggr(only({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>} ApprovedPC), ActivityID))

Regards.

Aggr Nested in Set Analysis

Mon, 14 Feb 2011 19:31:51 GMT

In that case, I'll suggest changing the order of functions.

aggr(Sum({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>} ApprovedPC), ActivityID)

Aggr Nested in Set Analysis

Tue, 15 Feb 2011 09:46:57 GMT

Hi Karl,

Thanks for the pointer ... I always viewed AGGR() as similar to Group By in SQL ... such that if your data is a flat file you could return the unique value for something by its dimension ... in my case ... ApprovedPC will be unique per ActivityID but due to other related data there are multiple records in the table.

I am afraid however that the syntax just returns 0 ... in other scenarios an expression such as sum(aggr(ApprovedPC, ActivityID)) is working fine and calculating correctly so I think I have the AGGR concept right for the data structure ... just can't get it working in tandem with the sets.

Thanks

Lee

Aggr Nested in Set Analysis

Tue, 15 Feb 2011 09:48:07 GMT

@NMiller - nope ... just returns a missing value ... does not evaluate.

Aggr Nested in Set Analysis

Tue, 15 Feb 2011 11:29:36 GMT

The thing I don't get is ... the selected year ... works fine with the AGGR() just as it is ... why does it fail when you do SelectedYear -1 ... even if that is passed into a variable and the variable is called in the SET Expression ... it fails.

A text box with the variable and with a simple SelectedYear -1 calculation show that the year is evaluating correctly ... the dynamic header works on the table using the variable ... and yes, there is data for that Year.

Aggr Nested in Set Analysis

pover — Tue, 15 Feb 2011 22:46:20 GMT

Hmm..so maybe something is missing in the set analysis. If the current year works and the previous year doesn't are you sure there is not a selection that is interfering with returning last year's data? What do you get if you try the following expression.

=Sum({1<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>}aggr(ApprovedPC, ActivityID))

That is the reason why you sometimes have to clear selections in set analysis. For example, if ActivityStartMonth would be interfering, you would clear it in the following manner:

=Sum({$<ActivityStartMonth=, ActivityStartYear={$(=Only(ActivityStartYear)-1)}>}aggr(ApprovedPC, ActivityID))

Try that and we'll keep on thinking of other possible reasons....

Regards.

Aggr Nested in Set Analysis

Thu, 17 Feb 2011 12:09:29 GMT

Hi Karl,

This is what we have got to so far and we have tried all sorts of variations in between ...

SUM({1<ActivityStartYear={$(=only($(vPreviousYear)))}>} aggr(ApprovedPC,ActivityID))

We have been checking the data, just in case, and there is definitely correct data for the all the years and we know what it should be adding up to ... getting quite frustrated with it now ...

Aggr Nested in Set Analysis

Thu, 17 Feb 2011 15:17:10 GMT

This is not a useful answer but I am experiencing exactly the same problem in trying to report on the previous year's sales. If I just use "sum" without the aggr function, it gives me a value, but it is too high because of the way the records have been split into several lines. But whenever I try to use the aggr function, I always get a value of 0 for the previous year's sales.

Aggr Nested in Set Analysis

Thu, 17 Feb 2011 15:32:20 GMT

That is exactly what is happening with me ... I sympathise as it is quite a road block now ...

Aggr Nested in Set Analysis

pover — Thu, 17 Feb 2011 17:31:30 GMT

Can anyone upload a small sample of the problem so that we can end the frustration?

While we wait for that, what would happen if you had year to the aggr()?

=Sum({$<ActivityStartYear={$(=Only(ActivityStartYear)-1)}>}aggr(ApprovedPC, ActivityStartYear, ActivityID))

Regards.

AW:Re: Aggr Nested in Set Analysis

brenner_martina — Thu, 17 Feb 2011 17:36:31 GMT

Hi,

I think the right use of Aggr is like this:

Sum({Set} (AGGR( Sum( {Set} Fieldname), Dim1, Dim2))

I miss in your expression the second part inside the Aggr-function: Sum( {Set} Fieldname) or Avg ...

Aggr Nested in Set Analysis

Thu, 17 Feb 2011 17:40:35 GMT

Hi Karl ... I am really sorry but I can not ... I have not got a good way of presenting a 'secure' version of the data as we have quite strict confidentiality.

I will try and add year to the grouping but I still come back to why it works on the selected year and not the selected year -1 ... I find that really odd.

Thank you for your help though in the meantime.

Aggr Nested in Set Analysis

pover — Thu, 17 Feb 2011 18:54:59 GMT

OK. We need to close this so I made sample that I hope reflects your problem. Review it. The formula is as Martina had mentioned above and I just used the only that I suggested earlier instead of the sum().

Regards.

Aggr Nested in Set Analysis

Fri, 18 Feb 2011 11:56:11 GMT

I tried Martina's formula and it works perfectly! The missing link was having to include "sum" again for the second part of the expression. Thanks Martina and Karl for solving this problem.