olap lookup question

[former_member1173607]

Hello.

I am trying to calculate the average salary for individual jobs and use that average in the record statement. My script incorrectly sums all jobs for the average.

Example:  We have Headcount in 2 Jobs - 5 in JobA with $10,000 salary and 5 in JobB with $20,000 salary.  The value this script returns is the total salary for all employees in both jobA and JObB - $150,000.  I want it to return a separate number for Job A ($10,000) and jobB ($20,000). That is, I need the value in BASESAL to be different for each job.  The script returns the same number for each job title.  What do I need to do to the lookup in order to get the correct amount for each Job?

Here is what I've written:

*CALC_EACH_PERIOD

*XDIM_MEMBERSET TIME=%YEAR%.JAN,%YEAR%.DEC

*XDIM_MEMBERSET DATASRC=INPUTORLOAD

*XDIM_MEMBERSET CATEGORY=Budget

*XDIM_MEMBERSET PAYACCOUNT="HEADCOUNT"

*XDIM_MEMBERSET SCENARIO <> BASELINE

*XDIM_MAXMEMBERS ORGANIZATION = 300

*OLAPLOOKUP PAYROLL

*DIM BASESAL:PACCOUNT="SALARY"

*DIM BASESAL:JOB = JOB

*DIM BASESAL: POSITION = "P_TOTCO"

*DIM BASESAL: SCENARIO="BASELINE"

*DIM BASEHDS:PACCOUNT="HEADCOUNT"

*DIM BASEHDS:JOB=JOB

*DIM BASEHDS:POSITION = "P_TOTCO"

*DIM BASEHDS:SCENARIO="BASELINE"

*ENDLOOKUP

*WHEN SCENARIO

*IS <>"BASELINE"

     *WHEN PAYACCOUNT

     *IS "HEADCOUNT"

*REC(PAYACCOUNT="SALARY",POSITION="P_NOPOSITION",EXPRESSION = %VALUE% * LOOKUP(BASESAL) / LOOKUPBASEHDS))

*REC(PAYACCOUNT="HEADCOUNTFTE",EXPRESSION = %VALUE%)

     *ENDWHEN

*ENDWHEN  

*COMMIT

[former_member1173607 · Accepted solution]

Here are the contents from the log file.  I note that the JOB dimension is missing:

select

non empty {[DATASRC.H1].[800DIPS],[DATASRC.H1].[Allocations],[DATASRC.H1].[ALLOCEXP],[DATASRC.H1].

non empty {[PACCOUNT.H1].[SalaryBasePay],[PACCOUNT.H1].[HeadcountApproved]} on 1,

non empty {[POSITION.H1].[P_CenturyLink]} on 2,

non empty {[SCENARIO.H1].[Baseline]} on 3,

non empty {[TIME.H1].[2012.JAN] on 4

from PAYROLL

where

([CATEGORY.H1].[Budget],[ORGANIZATION.H1].[E0011002],[RPTCURRENCY.H1].[LC],[MEASURES].[SIGNEDDATA])

[JohnL · Accepted solution]

Hi Mike,

Are JobA and JobB base level members?

Is JOB the parent member of JobA and JobB?

Can you comment out both lines, to see if the values get correctly queried?

i.e.

//*DIM BASEHDS:JOB=JOB

//*DIM BASESAL:JOB=JOB

Note: Since you do not directly scope in the BASELINE scenario

( *XDIM_MEMBERSET SCENARIO <> BASELINE )

This statement is redundant and can probably be removed

*WHEN SCENARIO 

*IS <>"BASELINE"

Thanks,

John

[former_member1173607 · Accepted solution]

Thanks John.  JobA and JobB are base level members.  "JOB" is the dimension name.  Commenting the dimension out of the lookup didn't help.

I also tried adding:

*WHEN JOB

  *IS *

- no help.

[JohnL · Accepted solution]

Hi Mike,

The MDX query looks off and you may need to correctly specify all dimensions in the OLAPLOOKUP.

non empty {[DATASRC.H1].[800DIPS],[DATASRC.H1].[Allocations],[DATASRC.H1].[ALLOCEXP],[DATASRC.H1].

non empty {[PACCOUNT.H1].[SalaryBasePay],[PACCOUNT.H1].[HeadcountApproved]} on 1,

non empty {[POSITION.H1].[P_CenturyLink]} on 2

What dimensions / Dimension member do you need to look at a total / aggregated level for your calculation? (Only  POSITION = "P_TOTCO" ?).

You could use an alternative and equally effective CALC_DUMMY_ORG for your POSITION dimension (Use GET(POSITION="#P_TOTCO") in your rec statement).

*CALC_DUMMY_ORG

                *ORG {dimension}={property}

                [*NOSCAN]

    [*WHERE {dimension} = {member set}]

                [*WHERE {dimension} = {member set}]

                …

*ENDCALC

Make sure you use the *NOSCAN instruction in your *CALC_DUMMY_ORG definition since you do not want to scan through your calculated values in memory but only use them in a GET statement.

HTH,

John

[former_member1173607 · Accepted solution]

What dimensions / Dimension member do you need to look at a total / aggregated level for your calculation? (Only  POSITION = "P_TOTCO" ?).

"P_TOTCO" is the calculated parent member of the POSITION dimension.  The aggregate dimensions I need are  Organization, Job and Time (for all Position members).

What I'm getting with my OLAPLOOKUP is aggregated Organization and Time without Job.

So what I'd like is to  sum salary

     By Organization

     By Job

     By TIme

I am only including POSITION = "P_TOTCO" to get aggregated positions for all members with the same Job in a single Organization.  Many positions hold thesame job title.

Here's a sample:

Org    Time   Position    Job                Salary

A        Jan     01           Admin Asst    15,000

A        Jan     02            Admin Asst   25,000

A        Jan     01           Manager        50,000

Org Total                                           90,000

My query returns    Job Admin Asst salary = 90,000

                             Manager Salary           = 90,000

Instead of separate total for each job.

[JohnL · Accepted solution]

Hi Mike,

Your example has Org: A and Time: Jan. Those are base level members right?

Is the position dimension, the only dimension you require an aggregated/rolled up/summarized value for?

Meaning this would also get you your correct value:

Org    Time   Position               Job                Salary

A        Jan     P_TOTCO           Admin Asst    15,000

A        Jan     P_TOTCO          Admin Asst   25,000

A        Jan     P_TOTCO          Manager        50,000

Org Total                                                     90,000

If so, you could use a with your position dimension and you could reference the summarized/total value of #P_TOTCO in your *REC statement.

Thanks,

John

[JohnL · Accepted solution]

Hi Mike, were you able to figure this out ?