ANY for List WIthout Top Level

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

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

Tagged:

Best Answer

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

     

    jasonblinn_2-1597088168599.png

     

     

     

    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.

     

    jasonblinn_0-1597087998826.png

     

    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. 

    jasonblinn_1-1597088121656.png

    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

     

     

     

Answers

  • jasonblinn
    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
    rob_marshall

    @jasonblinn @MarkTurkenburg 

     

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

  • MarkTurkenburg_*8288
    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. 

    SS1.PNG

     

    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

    SS2.PNG

     

    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

     

    SS3.PNG

     

    Thanks

     

     

     

     

     

  • rob_marshall
    rob_marshall

    @MarkTurkenburg 

     

    Why not just add a top level?

  • MarkTurkenburg_*8288
    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

     

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

Categories