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

Accepted answers

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

All comments

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:

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"
Set the value for B_Item to B
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,

AkhilEmmanual

Hi @Jesse251 ,

Regards,

Akhil

TristanS

@Jesse251 Alternatively you can do the following:

Add Product list in the Applies To section (highlighted in yellow)
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.
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
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.

Quick Links

Can you help?

Unanswered questions

Different Subsets of Parent Lists in Input & Output Modules
I have 2 lists used in modelling, In Input module I'm using subset of territories from Country List and subset of Regions from Flat list. But in Output module,Its reversed, I'm using subset of regions from Country list and subset of territories from flat list.How to derive the numbers from input to output module. I do have…
Is there a way to turn off the Show Comments Summary panel in UX pages with Comments?
This shows all comments across all data to all users with access to the UX page. This is not appropriate for any UX which might contain security access sensitive data. Comments on grids and cards ARE security dependent. That works fine. But anyone with access to the page can open this Summary Panel and see ALL comments…
Are there any plans to support actions to rename or delete native Versions?
If anyone is aware of idea exchange ideas that are currently being evaluated by the product team, if you don't mind linking them here, would love to give them an upvote. Thanks!