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

subodhp

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.

 

 

 

PaulRitner

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