topic Re: Using drill-in groups in expressions in QlikView

Using drill-in groups in expressions

Thu, 19 Jul 2012 20:31:34 GMT

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.

Re: Using drill-in groups in expressions

Thu, 19 Jul 2012 20:56:03 GMT

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.

Re: Using drill-in groups in expressions

Thu, 19 Jul 2012 20:56:11 GMT

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.

Re: Using drill-in groups in expressions

Thu, 19 Jul 2012 20:58:09 GMT

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.

Re: Using drill-in groups in expressions

Thu, 19 Jul 2012 20:58:35 GMT

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.

Re: Using drill-in groups in expressions

Thu, 19 Jul 2012 21:13:46 GMT

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