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

subodhp

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.

 

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 

Please let me know if i am doing something wrong.

 

Thank you.

PaulRitner

Hi,

 

can you show the blueprint view for Sales Range Setup?

 

Thanks

subodhp

Here you go:

 

 

 

PaulRitner

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

subodhp

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.

PaulRitner

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.