Add formulas to existing fields in script

[#1]

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

[#2]

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.

[#3]

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,

[#4]

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

[#5]

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.

[#6]

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

[#7]

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

Kiran.

[#8]

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?

[#9]

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.

[#10]

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,