Using a variable for Dimension in Aggregation

[zagzebski]

I have the following expression:

Sum(
           Aggr(
              if(sum({<[Year] = {$(=Max([Year])-1)}>}[Commission])>0,
                 sum({<[Year] = {$(=Max([Year]))}>}[Commission]),0),
                             [Policy Type],[Customer Name],[Department Name])

This expression works fine. However where "Department Name" is I want a variable based on a cycle group (which is the dimension in this object). So each time the cycle is changed it is reflected via a variable in the expression above.

What is the best way of doing this?

[Gysbert_Wassenaar]

If you have a variable vField defined as something like this: =getcurrentfield(MyGroupNameHere)

then you can use that variable like this:

Sum(
           Aggr(
              if(sum({<[Year] = {$(=Max([Year])-1)}>}[Commission])>0,
                 sum({<[Year] = {$(=Max([Year]))}>}[Commission]),0),
                             [Policy Type],[Customer Name],$(vField))

edit: to take field names with spaces or other weirdness into account try changing the variable to ='[' & getcurrentfield(MyGroupNameHere) & ']'

[zagzebski]

Thanks for the response. I am really close

It appears that it works for selections (within the cycle group) that do NOT have any spaces. It doesn't work for selections such as "Line of Business". I tried $([vRetensionDimension]) with brackets but that didn't seem to work.

[#4]

Hello,
I created a group a cycle group, and tried to create the variable with getcurrentfield, but QV doesnt recognize the name of my group in the expression.