How can I apply custom calculations using the GetFieldSelections?

Wed, 23 May 2012 16:51:03 GMT

How can I apply custom calculations on Line of Business (LOB) and other hierarchies, which are dependent on the current filters that are being applied to the underlying pivot tables?

I was able to successfully perform these calculations across three hierarchies using the GetFieldSelections feature.

As shown below:

rangemax(0,num(if(isNull(GetFieldSelections([Current LOB Level 1]))=0,sum({$<[Within LOB XFR]={1}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([Current LOB Level 2]))=0,sum({$<[Within LOB XFR]={2}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([Current LOB Level 3]))=0,sum({$<[Within LOB XFR]={3}>} [Sum(BaselineHC)] ))))

rangemax(0,num(if(isNull(GetFieldSelections([AMOC Manager 1]))=0,sum({$<[AMOC XFR]={1}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([AMOC Manager 2]))=0,sum({$<[AMOC XFR]={2}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([AMOC Manager 2]))=0,sum({$<[AMOC XFR]={3}>} [Sum(BaselineHC)] ))))

rangemax(0,num(if(isNull(GetFieldSelections([Function Level 1]))=0,sum({$<[FV XFR]={1}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([Function Level 2]))=0,sum({$<[FV XFR]={2}>} [Sum(BaselineHC)] )))) +
rangemax(0,num(if(isNull(GetFieldSelections([Function Level 2]))=0,sum({$<[FV XFR]={3}>} [Sum(BaselineHC)] ))))

However the users are indicating that I now have to modify the counts so they return a distinct value per employee.

I am not sure how to enforce the distinct value in this situation as the count bridges three hierarchies.

Has anyone else encountered this design issue and if so what was your strategy to resolve this problem?

topic How can I apply custom calculations using the GetFieldSelections? in QlikView

How can I apply custom calculations using the GetFieldSelections?