I have a table bringing through all of my nominal ledger transactions and want to create a P&L account off the back of it. As multiple nominal codes go into each section is there an easy way of doing this in the script.
ie. cost of goods sold = Nominal codes 1000+2000+3000+4000
Overheads = Nominal codes 1500+2500+3500+4500
The nominal codes are just a field in the transaction table but if I could create the expressions above in the script that would be great?
So you mean that when the code ends with "500" you want to add it to overhead, and if it ends with "000" you want to add it to COGS? You could just check this with Right(code,3) if that's the case. Or was this just an example and the codes are truly arbitrary?
Well if it really is arbitrary, then I don't see a way to automate filtering in the script, sorry.
This sort of calculation (just addition) is pretty light so it shouldn't be a problem to do this as an expression. If you just want to do the exact same thing in the script, there are several options. You could create a variable after your load and set it approximately like this: LET vCOGS = '=SUM(if(code=1000 or code=2000,fieldValue))';
This should result in a variable that contains the sum for the relevant fields. Depending on your other fields, you might also be able to do this with a group by. But like I said, simple addition of fields is probably best left for expressions (and will certainly be easier).
Thanks doing the calculation is the easy bit in the expression as you say,my issue is more trying to display in a format that the People viewing would recognise
ie.
Sales 1000
COGS 900
Gross Margin 100
In a nice reading down table (like excel basically)
Think I will have to persuade them otherwise - thanks for your speedy responses
