Hi again Jetsupporters,
I am working on a Jetreport that will show per customer the expected turnover from start budget year by the end of the current month versus the budget. This report calculates per customer the amounts (shipped and invoiced)+(shipped not invoiced)+(deliver due till end of month)-/-credited amount. This is then compared with the budget till month end.
The problem I am trying to solve is that the sales budget is devided in 12 periods (one for every month) and all budget amounts for one customer/product group are in one record in 12 separate colomns. Now if it is period 3, I want to sum the budget amounts for a customer in the colomns 1..3. In period 4. I want to sum 1..4 etc.
Here is the formula that works for the report that only looks at the current month:
=NL("Sum";"Sales budget cross company";$G$11;"Sales Budget Name";Options!$D$9;"Customer";"@@"&$D25)
In G11 the colomn name is made that refers to the colomn name in the sales budget table for the current month
In Options!$D$9 you will find the sales budget name for the current budget year which is at the moment B2008
In D25 is the customer number.
The sum function is need because a budgetline is made per customer per product group
I thought of solving it with an excel IF function but I am afraid that I will need much more that the allowed number of characters in a cell because that would take 12+11+10+9+8+7+6+5+4+3+2+1 NL functions to do the trick.
Any suggestions?
Have nice weekend anyway! :)
Arnoud
Date
Votes
4 comments
-
Jet Reports Historic Posts Arnoud,
I think we need more information. Can you list the field names of the columns and some information about the database, is it Nav, GP, SOL, Access, Etc? How do you know what period you are in? If you were going to write a big IF statement, what would it look like (maybe just the first couple of conditions)?
Anyway, hopefully with a little more information, someone can help you. -
Jet Reports Historic Posts We are using Navision 3.70 (with over 3500 changes). The field names in the Sales budget cross company table are: P1 Amount; P2 Amount; P3 Amount up to P12 Amount. This is how a NL function would look like if I only needed to sum it for one period for a specific customer:
for period 1
=NL("Sum";"Sales budget cross company";"P1 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")
For period 2:
=NL("Sum";"Sales budget cross company";"P2 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")
For period 3:
=NL("Sum";"Sales budget cross company";"P2 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")
I made a list in the option sheet to link P1 Amount to january, P2 amount to February etc. and with the current date I can determine in which period we are living. At the moment (March) it is period 3. The report returns P3 Amount in Cell C3. Next month it returns P4 Amount in C3.
What I want is that report returns the cumulative budget till month end. In P1: only P1 Amount; in P2: P1 Amount + P2 Amount; in P3: P1 Amount + P2 Amount + P3 Amount.
Here comes the big IF statement with more than 255 characters and cannot do the job for that reason:
=IF(C3="P1 Amount"; NL("Sum";"Sales budget cross company";"P1 Amount";"Sales Budget Name";"B2008";"Customer";"62100102");IF(C3="P2 Amount";=NL("Sum";"Sales budget cross company";"P1 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")+NL("Sum";"Sales budget cross company";"P2 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")…..etc up to IF(C3="P12";<12 NL functions>)))))))))))
Best regards,
Arnoud -
Jet Reports Historic Posts Hey everyone, I thought I would take a wack at this one.
I think trying to put everything into one cell is a mistake and you need to split it up a little.
First, I would create 12 columns (they can be hidden), with the first column having the following formula.=NL("Sum";IF(VALUE(RIGHT($C$3;LEN($C$3)-1)) >= 1;"Sales budget cross company";{""});"P1 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")and the second column having=NL("Sum";IF(VALUE(RIGHT($C$3;LEN($C$3)-1)) >= 2;"Sales budget cross company";{""});"P2 Amount";"Sales Budget Name";"B2008";"Customer";"62100102")and the so on for the other columns. This tell each column if they should do a sum or not. With this, it is just a matter of using the excel SUM or SUBTOTAL functions to add up the columns.
Does this make sense? or more important, does this work? -
Jet Reports Historic Posts Hi Emis,
It works. Thanks!
Arnoud
Please sign in to leave a comment.