Summing only some lines in a list

Jesse251

Hi everyone,

It's similar to SUMIFS with 1 criteria as a line item and another criteria as a line in a big list.

I've tried SUM, LOOKUP but none seems to work.

the error is: This … is not a line item

could you please see if you can help?

Many thanks,

Jesse

AkhilEmmanual

Hi @Jesse251 ,

You can create a list subset(items you need to get data for) . Take that as dimension and create a line item there format it as list and apply formula( item(list) ) . This line item you can use for lookup like you did before .

Regards,

Akhil

vishal3120

What I can understand is that you want to produce output for only some of the list items.

Solution: Create a System module with list and line item as 'boolean'. Tick the boolean against those list items you want result for.

Coming back into your module and write the formula as : IF SystemMod.Boolean[LOOKUP: Module.Line Item] THEN 1 ELSE 0

Jesse251

hi Vishal,

Thanks a lot for the reply. So this is what I'm trying to do.

In a simplified pivot table below, I'm trying to refer to data in cell B2 only

the syntax I tried is

Module.Line2[SUM: B] or

Module.Line2 [LOOKUP: B]

If I use your suggestion, it'll produce List.B, wouldn't it?

Sorry if I didn't explain it clearly in the previous post.

Again, thanks a lot.

TristanS

@Jesse251 I'm reading between the lines on the error you are getting and the formulas you've provided.

You mentioned the error you are getting is "This … is not a line item"

AND your formula is Module.Line2 [LOOKUP: B]

Is the error against B? I know you only provided a dummy sample formula but in your example B is a list which is why you would get that error. If I am correct then you can do the following:

1. Create a new line item, e.g. "B_Item" and set format to the list of what B is. For now just create this in a new module, e.g. "SYS01 Constants"
2. Set the value for B_Item to B
3. Update your formula to Module.Line2[LOOKUP: 'SYS01 Constants'.B_Item]
   

Jesse251

hi @TristanS,

Yes, you are right. It's the error message against B, which is just a line in my big list.

• thanks a lot for the suggestion. It works after I created a new module to refer to B. I'm able to retrieve B2.
• however, my list is quite long and I need to refer to not just B2 but at least 10 items or more on that list. Do I need to create a new module for each of them every time or is there any other way I can do that in Anaplan?

Best,

TristanS

@Jesse251 Alternatively you can do the following:

1. Add Product list in the Applies To section (highlighted in yellow)
2. Add a boolean line item "Do a lookup?" Not sure if you will need this or not. If you only want to get the value of "some" (not all) items then you need this. If you are getting value for all items in the list then you don't need this.
3. Add the "Item" line item below with formula. If you are getting the value of all items in the product list then you don't need the IF condition
4. The line item "Lookup other module value", you can use that lookup formula you have but have "Item" as the parameter for the lookup function

I would recommend you take the Anaplan Level 1 and Level 2 model builder courses if you haven't done yet. Refer to links below

https://learning.anaplan.com/mod/scorm/view.php?id=14712

https://learning.anaplan.com/course/view.php?id=1297

Jesse251

Hi Akhil, Tristan,

This is very helpful.

I will complete level 1 soon and will do level 2 as well.

Thanks a lot.