Level 3 Model Building User Story 1.7 - 1.10

Hi Community,

 

I had a question regarding the Override, Hold and Redistribution factor module using TAR02 and TAR03 modules. Is there a function that enables a SUM process excluding accounts that have Override or Hold selected?

 

I tried a SYS module using the List Lookup as a potential to overcome this problem. I had two list formatted line items on A1 Accounts. One showing all the accounts, another with an IF statement to exclude the list item if the Override Boolean is checked off. The idea was, that this might enable me to SUM on all accounts, subtract the override value and then SUM on all accounts except the account that was overridden. This was the first step before building in the Hold factor, but my redistribution factor did not line up to the training example.

Thoughts or additional feedback? Thanks in advance!

Comments

  • @aahmad 

     

    Its little tricky. I would say  aggregate the targets for all countries first and then aggregate held or revised data for all countries and then find the diff between the two. 

    And then next steps would be allocate this delta into multiple accounts as per the factor.

     

    Few Hints:

    Write  IF else statements on Held or Revised data line item

    Don't create any subsidiary views, exception for Country & Segment line items

    Don't use SUM & LOOKUP in the same line item

  • @Misbah 

    Thanks for the follow up! I'll try this out and see how far along I go, much appreciated!

  • Hi @Misbah @aahmad 

     

    I am an enthusiast who enrolled in talent builder program to learn more about anaplan, I am stuck in User stories 1.7-1.10, Need Help regarding the same:

    My approach to calculate redistribution factor:

    1) Aggregate Override & Hold values in "To Change" line item and subtract it from SUM of "Initial Accounts sales Target".

    2) Then divide it from the ones where "initial account sales target" do not have Hold or Override Value.

     

    Got Stuck at:

    1) To calculate sum of "Initial account sales target" i need to use lookup but when i use formula - Initial accounts sales target[lookup: A1 Account] but it is showing error.

    2) To calculate the denominator do i need to use IF Else statement - IF NOT Overide And Hold then sum initial sales target. But how to put it in a formula?

     

    Any Hint/Clue is much Appreciated.

     

    kunal_311_0-1618754747412.png

     

    kunal_311_1-1618754746771.png

     

     

  • @kunal_311 

     

    1. There should not be the need of using LOOKUP while calculating Initial Account Sales Target as Account-Products dimensions has Account as Parent, you can simply aggregate it to Account Level.

    2. This is the most important piece - If you are able to figure out how to achieve this on your own, it will be give boost your confidence. All I can say is that it all revolves around SUM & LOOKUP, not in one line item though. LOOKUP in current module and the SUM in TAR03 module ( and of-course IF ELSE Statement)

     

    Misbah

     

     

     

  • Hi @Misbah,

     

    How are you?

    I have a question about Held or Revised. Does this item contain only Hold or Override Value? What is the item "To Change"?

    Please give me some hints. 

     

    Thanks.

     

    Wei

     

     

  • Thank you for this question and all those who replied.