Aggr Nested in Set Analysis

[#1]

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

[#2]

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.

[pover]

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.

[#4]

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

[#5]

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.

[#6]

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

[pover]

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.

[brenner_martina · Accepted solution]

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

[#9]

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.

[pover]

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.

[#11]

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.