topic Re: Add formulas to existing fields in script in QlikView

Add formulas to existing fields in script

Mon, 07 Nov 2011 07:31:16 GMT

I have a field called Accounts with a number of account descriptions like Gross Profit and Invoicing. I have another field called Amount which is the amount for each account. I would like to create a couple of ratio formulas one of which is Gross Profit / Invoicing and I thought this would be best to do in the script.

Ultimately I would like for these formulas to be in the same field as Accounts so that they all get displayed as a dimension. I think that this will probably involve an inline load but I am having trouble with the expressions.

Could someone please explain the best way to go about this.

Thanks very much

Re: Add formulas to existing fields in script

Mon, 07 Nov 2011 07:54:37 GMT

Hi Fiona,

Lets say you have a table in script called Accounts with fields Customer,Product,AccountDescription,Amount.

Adding the below script would create the ration you need in the table.

Temp:
Load Customer,Product, Amount as AmountGP Resident Accounts
where AccountDescription = 'Gross Profit';
Join(Temp)
Load Customer,Product, Amount as AmountInv Resident Accounts
where AccountDescription = 'Invoice';
Concatenate(Accounts)
Load Customer,Product,'GP/Invoice' as AccountDescription,AmountGP/AmountInv as Amount Resident Temp;
Drop Table Temp;

Hope this helps.

Kiran.

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 00:04:40 GMT

Hi Kiran

Thanks very much that's part of the way there. I've got GP% label in the Accounts field now but it is not calculating correctly. As the expression I am using for the rest of the dimension is sum(Amounts) I think it is working out the GP% for every line and then summing them together.

I've attached the file as I'm sure this will be easier to work out if you can see what I'm trying to do.

Would this be more suited to a variable? I haven't worked with them very much.

Fiona,

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 00:47:05 GMT

I started to play around with the variable idea and I've now included the following:

let varGP% = Num(AmountGP/AmountInv, '0.0%);

And I changed the concatenate scripting to read instead of Amount GP/AmountInv as Amount

to vGP% as Amount

This however is erroring when I reload.....

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 01:37:27 GMT

Hi Fiona,

In addition of dividing in the script keep two fields, AmountGP and AmountInv. In the table change the expression to:

=if(Account='GP%',num(sum(Amount),'#,##0'),num(sum(AmountGP)/sum(AmountInv),'0.00%'))

Your script would be:

Concatenate(Summary)

LOAD

Segment,

Entity,

Type,

Month,

'GP%' as Account,

Num(AmountGP/AmountInv,'0.0%') as Amount,

AmountGP,AmountInv

Resident Temp;

Regards,

Kiran.

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 01:58:19 GMT

right even closer now...

its got the right amount but it won't format as percentage - its coming through as an integer rather than a percentage. I've had a look and made sure that both the script and the expression state ('0.0%).....???

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 02:04:37 GMT

If you are using my earlier expression, number format should be expression default. Didd a little demo in the attachment.

Kiran.

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 02:29:41 GMT

Thankyou very much. The formula you used in your last answer was different to the second answer but I played around and it is now working correctly. Can I ask for one final favour though. I need to apply this to an expression that already has a filter.

Expression at the moment is:

sum(if(Type = 'Actual', Amount))

Obviously this won't work for the GP%. The formula to pick up the GP% is:

=if(Account='GP%',num(sum(AmountGP)/sum(AmountInv),'0.00%'),num(sum(Amount),'#,##0'))

When I combine the two its not liking something - can you see what it is???

(if(Type = 'Actual',Account = 'GP%', num(sum(AmountGP)/sum(AmountInv

),'0.00%'), if(Type = 'Actual', sum(Amount)))

Seems to be a problem with the nested if?

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 02:36:47 GMT

Yes, syntax error in the nested if. Try the below expression:

=if(Account = 'GP%', num(sum(AmountGP)/sum(AmountInv),'0.00%'),if(Type = 'Actual', num(sum(Amount),'#,##0')))

Regards,

Kiran.

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 03:31:30 GMT

Hi Kiran

Thanks but that's not quite the formula I need. I need Type = Actual to be an overriding filter for the whole expression and then to define it further to include the GP% as above.

If I was doing this in excel it would be:

=If(and(Type = "Actual", Account = "GP%", sum(AmountGP)/sum(AmountInv),if(and(Type = "Actual, Account <> "GP%", sum(Amount)

Thanks,

Re: Add formulas to existing fields in script

Tue, 08 Nov 2011 03:46:03 GMT

This would be the equivalent qv syntax:

=If(Type = "Actual" and Account = "GP%", sum(AmountGP)/sum(AmountInv),if(Type = "Actual and Account <> "GP%", sum(Amount)))

Kiran.