Calculations in the Load Script

[greend21]

I'm trying to do a calculation in the load script but I keep getting an invalid expression error. I thought it could be something with the dates being timestamps so I used floor to drop it and make it a number but that doesn't seem to be correctiong the issue either. Do you have any idea what the issue could be?

Sum(fabs(AllocAmt)*(Floor(ClosedDate)-Floor(TransEfftDate)))/Sum(Floor(ClosedDate)-Floor(TransEfftDate))

Thank you,

[vishsaggi · Accepted solution]

Can you share the script you are running and also a snapshot of your error msg? if you are using Sum() aggregate function are you using Group By in your script?

[balabhaskarqlik]

It'll throw invalid expression, cause data conversion not happened properly.

Create some variables like this and those.

Set vMaxInvoiceDay = Day($(vMaxInvoiceDate));

Set vDaysInMaxMonth = Day(MonthEnd(Month($(vMaxInvoiceDate))));

Set vRunRateFactor = ($(vDaysInMaxMonth) - $(vMaxInvoiceDay))/$(vMaxInvoiceDay);

[greend21]

I completely forgot about the Group By. Since I am loading a lot of other fields I just did the calculations on a temp table and left joined them back. Thanks!