My formula is not giving me the expected result, need some help (LIS, LOOKUP)

LouiseBourgonjon
Contributor

My formula is not giving me the expected result, need some help (LIS, LOOKUP)

Hi Anaplan community,

 

I am a bit puzzled with the result of a formula, which is not giving me what I expect.

I have a target module (O4. EB Summary) with two dimensions: a list with a year and a list with platforms.

I have 3 line items: "NY Plan IBT" and "EB IBT" are referring to a line item in another module (O1. EB output numbers), "Variance" is simply the difference between the two other line items.

  • Target module: O4. EB summary  [dimensions: T1 - Year, O2 - Platforms]
  • Source module: O1. EB output numbers (MM USD) [dimensions: T2 - Quarters, O2 - Platforms, LIS_EB Input]
  • System module used in the lookups: SYS 1 - Model Settings [dimensionless module]
  • T1 - Year is the parent of T2 - Quarters

 

Line item "NY Plan IBT"

formula: 'O1. EB output numbers (MM USD)'.IBA[LOOKUP: 'SYS1 - Model Settings'.'Select NY Plan Sub3']

refers to line item IBA in the source module with 3 dimensions:

1) quarters, which is the child of the year dimension in my target module, so I can use the hierarchy

2) platforms, same dimension as in my target module

3) the line item subset EB, I don't have this dimension in my target module, so I am using a LOOKUP to a system module with a line item which holds the value of that item of the LIS that I need. In this case, it is "NY Plan Sub3".

So far everything is going fine, the formula gives me the expected result.

 

Line item "EB IBT"

formula: 'O1. EB output numbers (MM USD)'.IBA[LOOKUP: 'SYS1 - Model Settings'.Select NY Pulse]

should  work in the same way, it refers to line item IBA in the source module with 3 dimensions:

1) quarters, which is the child of the year dimension in my target module, so I can use the hierarchy

2) platforms, same dimension as in my target module

3) the line item subset EB, I don't have this dimension in my target module, so I am using a LOOKUP to a system module with a line item which holds the value of that item of the LIS that I need. In this case, it is "NY Pulse".

Although this formula should work in the same way as the formula in the line item "NY Plan IBT", it is not. All results are zero. (and the source data is <> 0)

 

What am I doing wrong? Can anyone help me?

 

Attached you will find screenshots of my different modules;

 

Thank  you!

Louise

1 ACCEPTED SOLUTION

Accepted Solutions
LouiseBourgonjon
Contributor

@Misbah ,

Your suggestion to uncheck the box "Is Summary" worked.

I just had to go back to the very source module, not the intermediary one (which I previously checked but there was no Is Summary item there). 

My question is resolved. 

View solution in original post

7 REPLIES 7
Misbah
Moderator

@LouiseBourgonjon 

 

Can you check Source Line item in the Second case, if that is the summation of other lines. If so, then go to blueprint of the source module, uncheck the Summary box of that particular line item (Don't change anything else)

 

Hope that helps

Misbah

LouiseBourgonjon
Contributor

Hi @Misbah , 

 

Thank you for your quick reply.

The source line item is not a sum but a substraction. The 'Is Summary" box is not checked. 

See attachment. 

Misbah
Moderator

@LouiseBourgonjon 

In your screenshot I don’t see “Is Summary” checkbox. 

LouiseBourgonjon
Contributor

@Misbah , sorry, too quick, see attached

Misbah
Moderator

@LouiseBourgonjon 

 

Strange!! It should work

 

If its urgent then as a workaround you can use LOOKUP formula for each line in your target line item and deduce the final figure (Not recommended if its not urgent) but I would say get this checked by Anaplan by raising a ticket.

 

Misbah

 

LouiseBourgonjon
Contributor

Thank you for this suggestion @Misbah 

I have raised a ticket to Anaplan support.

LouiseBourgonjon
Contributor

@Misbah ,

Your suggestion to uncheck the box "Is Summary" worked.

I just had to go back to the very source module, not the intermediary one (which I previously checked but there was no Is Summary item there). 

My question is resolved.