SELECT not working when SUMming from a Module with a Numbered List

drock
drock
in Modeling

Fellow Anaplanners,

 

We have a summary module in our Headcount planning application which pulls from both the new and existing headcount module. The new headcount uses a numbered list, existing headcount module does not. We have two formulas documented below....in the first one, we are pulling from the existing headcount module which is using a regular list and it works perfectly:

 

'Employee Comp - Detail'.AOP Existing Comp[SUM: 'Employee Comp - Detail'.'Dept', SELECT: Version List.'CY AOP']

 

The next formula adds a SELECT for only taking the approved comp from the New Employee Expense:

 

New Employee Expense.Comp [SUM: New Employee Expense.Dept, 'SELECT: Status.Approved, SELECT: Version List.'CY AOP']

 

The result in the second formula pulls values, but completely disregards the SELECT and gives us an inflated value. Has anyone seen this before? Is there a workaround besides creating another module which changes out the numbered list for a static list?

 

Thanks in advance for your suggestions!

 

Tagged:

Answers

  • ChrisWeiss
    ChrisWeiss

    Hi,

     

    Another approach you can try is adding a new line item called 'Approved AOP Existing Comp' in the same module that has 'AOP Existing Comp', which says IF Approved THEN AOP Existing Comp ELSE 0.  Then you can use your same SUM and SELECT formula without adding the SELECT on Approved, just SUM on the 'Approved AOP Existing Comp'.

     

    Happy to provide more details if you'd like, hope that helps!

     

     

  • Ansar.Sayfin
    Ansar.Sayfin

    Hello!

    I feek the lack of details slightly - is status set up as dimensionality of the source module or it is an attribute? I assume if formula is not working then it is not a dimensionality, and that's why select does not work.

     

    This case you have 2 options:

    1) As it was described in previous comment - create line item where you put the formula "if name(New Employee Expense.Status)="Approved" then New Employee Expense.Comp else 0

    2) Create not dimensional module with 1 line item (name it as approved flag), put the fornula there = status.approved. Then upgrade your formula: New Employee Expense.Comp [SUM: New Employee Expense.Dept, SUM: New Employee Expense.Status, LOOKUP: New Module.Approved Flag, SELECT: Version List.'CY AOP']

     

    Thanks

Categories