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)





Best Answer

  • jasonblinn
    Answer ✓

    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. 







  • Hi @MarkTurkenburg 


    I think this is the formula you are looking for:


    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.


  • @jasonblinn @MarkTurkenburg 


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

  • 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







  • @MarkTurkenburg 


    Why not just add a top level?

  • 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






  • @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.

  • 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!



  • 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!