topic Calculations in Load Script in QlikView

Calculations in Load Script

Thu, 29 Apr 2010 17:13:11 GMT

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?

Any help much appreciated

Matt

Calculations in Load Script

vgutkovsky — Thu, 29 Apr 2010 22:52:18 GMT

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?

Regards,

Calculations in Load Script

Fri, 30 Apr 2010 07:21:30 GMT

Hi Sorry, slightly misleading there teh codes are arbitrary

Matt

Calculations in Load Script

vgutkovsky — Fri, 30 Apr 2010 08:02:22 GMT

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).

Regards,

Calculations in Load Script

Fri, 30 Apr 2010 08:09:15 GMT

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

Matt