Running Sum for upcoming months based on previous months

[montubhardwaj]

Hi All,

I need a help for figuring out running sum logic for my requirement. I have listed down my requirement below.

I have the Sales till last month and no need to worry about it. But, based on previous months sale, I have to forecast coming moth sales.

Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Sales

10

20

30

40

50

60

70

80

90

100

X

Y

Where X= {(Sum of all the moths so far)/No of Months so far}*No of weeks in this month

i.e. ((10+20+30+40+50+60+70+80+90+100)/10)*4)=220)

Y= {(Sum of all the moths so far)/No of Months so far}*No of weeks in this month

i.e. ((10+20+30+40+50+60+70+80+90+100+220)/11)*5)=350)

The value calculated for the month of Nov should also be included for Dec month calculations.

Thanks in advance for your advice.

[johnw]

I'm almost certain I've made some sort of error here, but maybe something along these lines:

if(sum(Sales),sum(Sales),avg(above(Sales,1,rowno()-1))*Weeks)

Even if you can get something of that form working, it's not really ideal, since the above() only works for rows shown in the chart. So if someone actually selects Oct through Dec, it would show different numbers for Nov and Dec than with the whole year selected. On the other hand, maybe that's what you want.

[montubhardwaj]

Hi John,

Thanks a lot for your time. I always admire your approach and like to see your response on forum.

The expression you suggested is not working(may be I am missing out something?). I think Avg is not working with Above(). it is not validating the expression.

Is there any approach we can avoid the use of above?

Many thanks in advance.

[johnw]

First, attached is a working solution using above(). You were right about avg() not working with above(). I had to use rangeavg() instead of avg(). I also had to refer to the column by number instead of name since I used the same name for the column as for a field, and it seemed to get confused by that. So this:

if(sum(Sales),sum(Sales),rangeavg(above(column(1),1,rowno()-1))*Weeks)

Now, if you still don't want above(), let's start over with the requirements. 220 and 350 seem like a really bad forecast of sales for November and December unless your company has some very, very strange expectations for sales. Why would you expect sales to shoot up like that? And if you don't have sales for November, why would you include an estimate for November when calculating December's number? There is no information for November. So using it literally adds no information to your forecast. So I am very suspicious of any forecast formula that behaves that way.

Off hand, it looks like you were shooting for an average of the YTD sales. So 55 for November, and 55 for December.

A better approach might be to use a linear trend line, producing 110 for November and 120 for December.

The reason I'm asking about this isn't just because what you're doing makes no sense to me, but because WHY you're doing it and what you actually intended may influence how we avoid using above(). I used above() because it was the only way that occurred to me to include November's forecast when calculating December's forecast. Simulating this without above() could be difficult. If it is the wrong requirement, I don't want to try to figure out something difficult, when I think the RIGHT requirement might be much simpler.

Or maybe you'll be satisfied with the above() solution, now that it's working as requested.

[montubhardwaj]

Thanks John. To answer you question, the data provided above is just dummy data but in actual scenario it will hit database to fetch data till last month. So just to predict the sales in coming month, they want to have this sort of calculation.

This formula is working fine for straight table. But I want to have it presented in Pivot form (please see the attached).But in that form of report, this formula is not working.May be I need to change the formula there but not sure.

Appreciate your help as always.

[johnw · Accepted solution]

Nothing was attached, but I can guess the problem. It works fine if the Month dimension is on the left. But when you move the Month dimension to the top, you've turned rows into columns, so you have to replace above() with before() and rowno() with columnno(). See attached.

[montubhardwaj]

Sorry John. Forgot to attach the file . But you guessed it rightly.yeah now it is working all fine. I was just wondering at your approach. it was good one. Thanks a lot for your help. You Rock !!