Using drill-in groups in expressions

[#1]

I have a drill-in group called d:Year.Quarter.Month defined on 3 fields (Year, Quarter and Month). I have an annual hours per FTE figure of 1,725 and I want to figure out the resource headcount needed across this group. I was going to use an expression that would include nested IFs to set the divisor to 1, 4 or 12 based on the value of the group "variable" but I can't select this as a field or variable in the Expression dialog.

There is probably a better way to do this than what I am trying to do, so I'm open to any help available here. I just want the FTE Demand to adapt in a pivot table chart based on the selected group.

Perhaps an AGGR function is needed, but so far, I'm really not comprehending its use.

[#2]

Update: I devised a method using the GetPossibleCount for the Year and Quarter fields that appears to work, though this may not be the best solution:

Sum

((Volume * Minutes / 60) / (1725 / If(GetPossibleCount(Year)>1,1,If(GetPossibleCount(Quarter

)>1,4,12))))

Volume is the number of contracts to be executed, Minutes is the number of minutes needed to execute a contract, and 1725 is the FTE hours per year.

[#3]

Update: I devised a method using the GetPossibleCount for the Year and Quarter fields that appears to work, though this may not be the best solution:

Sum

((Volume * Minutes / 60) / (1725 / If(GetPossibleCount(Year)>1,1,If(GetPossibleCount(Quarter

)>1,4,12))))

Volume is the number of contracts to be executed, Minutes is the number of minutes needed to execute a contract, and 1725 is the FTE hours per year.

[#4]

Update: I devised a method using the GetPossibleCount for the Year and Quarter fields that appears to work, though this may not be the best solution:

FTE Demand =

Sum(

          (Volume * Minutes / 60)

          /

          (1725 /

               If(GetPossibleCount(Year)>1, 1,

                    If(GetPossibleCount(Quarter)>1, 4, 12)

               )

          )

)

Volume is the number of contracts to be executed, Minutes is the number of minutes needed to execute a contract, and 1725 is the FTE hours per year.

[#5]

Update: I devised a method using the GetPossibleCount for the Year and Quarter fields that appears to work, though this may not be the best solution:

FTE Demand =

Sum(

          (Volume * Minutes / 60)

          /

          (1725 /

               If(GetPossibleCount(Year)>1, 1,

                    If(GetPossibleCount(Quarter)>1, 4, 12)

               )

          )

)

Volume is the number of contracts to be executed, Minutes is the number of minutes needed to execute a contract, and 1725 is the FTE hours per year.

[#6]

Hi Scott,

If you need to know the selected field within a drill-down or a cyclic group, you can use this function:

GetCurrentField("GroupName").