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

    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
          Isaiah Weed

          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
            Neil Miller

            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
                  Neil Miller

                  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
                      Karl Pover

                      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
                          Neil Miller

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

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


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

                             

                            • Aggr Nested in Set Analysis

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

                              Big Smile