Sum to get total sales based on a GL Value by month

Hi,

I am pretty new to Anaplan and i am hoping i can get help with a problem i am stuck at. Below is a mockup of my data

Module 1 Transaction 1 Transaction 2 Transaction 3 Transaction 4 Transaction 5 Transaction 6 Transaction 7
OP01 1 2 3 4 5 6 7
OP02 8 9 10 11 12 13 14
OP03 15 16 17 18 19 20 21
OP04 22 23 24 25 26 27 28
OP05 29 30 31 32 33 34 35
OP06 36 37 38 39 40 41 42
OP07 43 44 45 46 47 48 49
OP08 50 51 52 53 54 55 56
OP09 57 58 59 60 61 62 63
OP10 64 65 66 67 68 69 70
OP11 71 72 73 74 75 76 77
OP12 78 79 80 81 82 83 84
OPFY 17 17 17 17 17 17 17
GL Value 100000 400000 100009 410000 100345 400099 100000

This data is residing in one of my modules where OP01 - OP12 represent the month January to  December. The OPFY field represents the financial year. Transaction # is unique which i have as a list in my module and all the rows are line items.

 

I have a 2nd module which is time dependant which basically uses the data from the above module to calculate the total sales based on the GL value for the transaction. The module looks something like below

Module 2 Jan 2017 Feb 2017 Mar 2017 Apr 2017 May 2017
Total Sale 1          
Total Sale 2          

Where Total Sale 1 for each month is the sum total of all transactions in that respective month where GL value is in the range 100000 - 200000 so for the month of Jan 2017 it will equal to Transaction1 +Transaction 3 + Transaction 5 + Transaction 7 in the line item OP01 of module 1

Total Sale 2 for each month is the sum total of all transactions in that respective month where GL value is in the range 200001 - 500000 so for the month of Jan 2017 it will equal to Transaction2 +Transaction 4 + Transaction 6  in the line item OP01 of module 1

 

Now i am unable to think of what lists i should create for the same so that i can create module 2 and also how i would apply the SUM function based on the lists i create.

 

All help on this will be greatly appreciated.

 

 

 

Answers

  • Hi,

     

    I recommend something along the following lines:

    1. create a list named "Sales Ranges"
      1. Make this list numbered
      2. Insert a Top Level list item named "Total Sales" (or something descriptive)
      3. Add a text property named "Display By", and set it as the Display By property in General Lists.  We'll come back later and add the formula.
    2. Create a Module named "Sales Range Setup"
      1. Include our Sales Ranges List
      2. Add the following line items:
        1. Lower Limit (Format as Number)
        2. Upper Upper Limit (Format as Number)
        3. Display By (Format as TEXT)
          1. Insert a formula that creates a text string of the Lower & Upper limits above.  This way, you can change your limits on the fly and not have to update the list display by.
          2. Go back to the Sales Range List and add a formula to the Display By property that points to the Module's Display By Line Item.
      3. Set your lower and upper limits in this module and test to see if the display by in the list is updating as expected.
    3. Go to your Transaction Module (i.e. Module1)
      1. Add a line item called Range Mapping (Format to our Sales Ranges Numbered list)
        1. Set Time in Months (pretty sure it will have to be this way in order to sum into Months later)
      2. Insert a formula that compares the YEARVALUE of each Transaction to the SALES RANGE definitions... it might look like:  If YEARVALUE(GL VALUE) is => Range1.Lower Limit and YEARVALUE(GL VALUE) is <= Range1.Upper Limit Then Sales Ranges.Range1 else if YEARVALUE(GL VALUE) is => Range2.Lower Limit and YEARVALUE(GL VALUE) is <= Range2.Upper Limit Then Sales Ranges.Range2 ELSE BLANK.... at this point, you have effectively mapped your Transactions to your Sales Ranges... so you can sum them!
        1. Note:  if you add a new (i.e. third) range, then this formula would need to be updated. this isn't ideal, but the ranges should be fairly slow moving.
    4. Add a module named "Sales by Range"
      1. Include our Sales Ranges List
      2. Include Time in Months
      3. Add a line item named "Amount"
        1. Insert a formula something like:   Module1.Amount[Sum: Module1.Range Mapping].

    The above is one way to do this.  There's at least one other really different way that I can think of... gotta get on a call.

     

    Let me know if this helps.

     

    Paul

  • Hi Paul,

    I started doing the steps you have enlisted. I went through creating the Sales Ranges numbered list and then the Sales Range Setup Module. I then created a formula to point to property in the list to the line item in module. If i look at the module now, my Lower limit and upper limit number values but they cannot be changed. The Sales Range setup module i created has Sales Range List as columns and line items as rows. The display by is just a formula saying TEXT(Lower Limit) & "-" & TEXT(Upper Limit). 

     I didnt proceed to the next steps after that.

     

    Sales Range Setup.JPGSalesRange_List.JPG

    The next step calls for me to create a line item in module 1 which i started but after that stopped. Below is the line item i created 

    LineItem_module1.JPG

    Please let me know if i am doing something wrong.

     

    Thank you.

  • Hi,

     

    can you show the blueprint view for Sales Range Setup?

     

    Thanks

  • Here you go:

    Sales_range_Setup_Blueprint.JPG

     

     

     

  • Community isn't the most efficient method of communications....

     

    I can't see the whole blueprint.   Can you confirm the following:

    1. If versions are included, then confirm that you have write access.
    2. Anaplan Time shouldn't be an element
    3. You have administrative rights in the model
  • Sorry about that. I have exported the blueprint now and attached. Also i have full access. This module does not have versions. Only line items and the GL Range list as the only pieces that are part of the module.

  • ah, you have to add two items (in addition to the top-level item).  Each item you add to the list is a new range... in this way, you can have many ranges.