20 Replies Latest reply: Feb 18, 2011 7:03 AM by Lee Alderdice

# Aggr Nested in Set Analysis

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

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

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

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

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

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

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

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

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

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

• ###### Aggr Nested in Set Analysis

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

• ###### Aggr Nested in Set Analysis

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

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

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

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

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

• ###### Aggr Nested in Set Analysis

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.

• ###### Aggr Nested in Set Analysis

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

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

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.

• ###### AW:Re: Aggr Nested in Set Analysis

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

Martina/Karl ... brilliant! That has cracked it, thank you so much for your help on this ...