ANY for List WIthout Top Level

Hi Team,

I have a list without a top level (Ramp-Up %'s which do not require to be aggregated) but I need to be able to determine if any of the intersections have a value for a Filter Boolean

Is there a formula/method to return a Boolean if ANY item for a list has value in a module. This is calculated from a module without this dimension as it needs to be used for the Filter (which can't be set to Summary Level due to not having a top level)

Thanks

Mark

Accepted answers

jasonblinn

Got it.

Could you create a list that has a single list member in it and then use that with the ANY Formula to simulate a top-level? I have mocked this up and tried to re-create your naming conventions. I have the number input in the Value line item, and a Bool that just says: Value <> 0.

I then have a line item that is just a single list, with a single list member in it. My list just has a member called "A". I create a line that has my list member in it, and can use that in my ANY statement.

From there I have a module that is Dimensionalized by All of the other lists except for the Ramp up Dimension and I do also include my new dimension called "A". Now i can use the formula: Staging[ANY:A] and then use my A item in my filter where it was making you pick it before.

In the first screenshot, you can see that I typed in overridden numbers for New Business in ANZ L1 and Growth in Asia L1, and those are the two boxes that are checked in the third screenshot.

Not 100% sure if this gets you what you are looking for, but it is an interesting workaround to not having a top-level.

Jason

All comments

jasonblinn

Hi @MarkTurkenburg

I think this is the formula you are looking for: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/xANY_y.html

You can stage the data in your module by saying something like (Ill call this 'Staging'): Value <> 0

And then in a line that is dimensionalized by the list have a formula that reads something like: Staging[ANY:List Formatted Line Item]

Let me know if that isnt clear and I can mock something up for you.

Jason

rob_marshall

@jasonblinn @MarkTurkenburg

I think you will still need a top level for that to work.

MarkTurkenburg_*8288

Thanks @jasonblinn but I can't use the ANY function unfortunately, let me step out my issue a bit more clearly 😊

I have a module which is dimensionalised by 'Ramp-Up Month' which is the column dimension (1,2,3 etc). This dimension does not have a Top-Level as it is not required to be aggregated. The Line Item is an 'Override' Line Item & I need to produce a Filtered view of the module for any intersections whereby there is a value <> 0.

If I try to do a standard Filter straight out of the module I need to select which List items to use for the Filter & hence I cannot chose the Top Level for the Ramp-Up dimension

If I create a new SYS module without the Ramp-Up dimension I could use this for the Filter but I need to be able to return a TRUE for any intersection dimensions from the original module where ANY of the Ramp-Up items was <> 0. I can't use ANY here because I do not have the Ramp-Up list as a dimension

Thanks

rob_marshall

@MarkTurkenburg

Why not just add a top level?

MarkTurkenburg_*8288

I think I'll have to but was whether it would cause any adverse consequences. I think there are 2 that can be mitigated:

1) Adds to module space but by turning off SUMMARY this can be reduced (not appropriate in all circumstances)

2) Incorrect formulas from a module without the dimension referencing a module with the dimension will return a value (SUMMARY). Previously an error would occur & this would help assist you in remembering you should not have a dimension mismatch when this List is included

Thanks!

Mark

jasonblinn

Got it.

In the first screenshot, you can see that I typed in overridden numbers for New Business in ANZ L1 and Growth in Asia L1, and those are the two boxes that are checked in the third screenshot.

Not 100% sure if this gets you what you are looking for, but it is an interesting workaround to not having a top-level.

Jason

rob_marshall

@MarkTurkenburg

So yes, top levels are not the best thing to have, but very often you will need them. When to not use them? Basically any list where you don't need aggregate the data like Currencies or Transactional data. Also, and I pretty positive you aren't doing this, but when people use properties in the list and the format is Number. When that happens, every time a new member is added, the number properties get summed, whether you need them or not. And believe it or not, this is very taxing especially on a large list (>10 million members).

With that said, you can mitigate this by turning summaries off in a module as you alluded to so having that extra member doesn't penalize you.

MarkTurkenburg_*8288

Nice @jasonblinn!

That's a pretty cool solution, I would not of thought of using a Subsidiary view to attain this result but I can see how it works

Thanks for the effort involved in creating this!

Mark

MarkTurkenburg_*8288

Thanks @rob_marshall

Yes I can see that sometimes they may not be 100% ideal but it's not the end of the world to use them

Appreciate your help!

Mark

Quick Links

Can you help?

Unanswered questions

Can Please explain the difference between a general list,a module,and action in Anaplan.
Can please explain why we create a general list ,a module,and Action ,and what purpose each one serves.🙂
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…