Accumulated

Solved4.38K viewsFormulas and Functions
0

I am sending an example of a formula that I think that should work on the Quantrix, but I cannot put it to work.

The objective is to accumulate values. The user inputs the Sales Monthly and the systems should return the Accumulated.

Sales Accumulated:Month=SUM(Sales Monthly:January .. Sales Monthly:Month)
Or
Sales Accumulated=SUM(Sales Monthly:January .. Sales Monthly:Month)

Why this formula does not work?

Thanks,
Rui

1

Hello – thanks for your post:

You can accomplish this by incoporating recurrence along both the MONTH and YEAR dimension.

2 formulas are needed. One for the first 12 months, and then one ongoing. Take a look at the attached file and please post again if any questions…

Regards,

-Mike

0

Oops, no attachment. Let’s try that again.

0

Hello,

I’ve added another twist to this topic of “accumulated” formulas – see the attached model.

What I am trying to accomplish is a forecast of “recognized” monthly revenue. Monthly Sales would be recognized over 12 months. So, month #3 would have to sum 1/12 of months 1, 2 and 3. The problem is how to restrict this accumulation to only the previous 12 months.

Thanks for the help!
-Bryan

0

Hi Rui,

I have attached a second version of the file you submitted. You need to create an additional formula to accomplish this. The formula establishes a link between December of the PREVious year and January of THIS year.

That being said – the formula you created attempts to establish this year relationship in the same formula as the month summation formula – an interesting concept. I will analyze further to see if in future versions we can acommodate something like this.

Regards,

-Mike

0

Thanks it worked but now I want to complicate and accumulate year and Month and I am doing something wrong and I do not Know what:

Sales Accumulated since 2003:Month[THIS]:Years[THIS] = sum(Sales Monthly:Years[1]:Month[1] .. Sales Monthly:Years[THIS]:Month[THIS])

0

Hi Rui,

You can accomplish this by using recurrence (e.g., [THIS], [PREV]) in your formula.

Here are a couple examples:

Sales Accumulated:Month[THIS] = Sales Accumulated:Month[PREV] + Sales Monthly:Month[THIS]

or

Sales Accumulated:Month[THIS] = sum(Sales Monthly:Month[FIRST]..Sales Monthly:Month[THIS])

Please try this and let us know if we can help further.

Best regards,

Chris