Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Add formulas to existing fields in script

Hi

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

1 Solution

Accepted Solutions
Not applicable

Re: Add formulas to existing fields in script

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.

10 Replies
Not applicable

Re: Add formulas to existing fields in script

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.

Not applicable

Re: Add formulas to existing fields in script

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,

Not applicable

Re: Add formulas to existing fields in script

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

Not applicable

Re: Add formulas to existing fields in script

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.

Not applicable

Re: Add formulas to existing fields in script

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

Not applicable

Re: Add formulas to existing fields in script

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

Kiran.

Not applicable

Re: Add formulas to existing fields in script


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?

Not applicable

Re: Add formulas to existing fields in script

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.

Not applicable

Re: Add formulas to existing fields in script

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,

Community Browser