5 Replies Latest reply: Oct 18, 2017 4:37 PM by Joshua Russin

# Excel formula equivalent in a script?

Hey everyone,

I am trying to see if there is a way to manipulate this into a script somehow. I am trying to create this WIP Inbound column. The formula is

WIP Inbound = (Today's WIP - (Yesterday's WIP - Today's Processed))

Thanks!

• ###### Re: Excel formula equivalent in a script?

May be this?

Sum({<Date = {'\$(=Max(Date))'}>} WIP) - (Sum({<Date = {'\$(=Max(Date -1))'}>} WIP) - Sum({<Date = {'\$(=Max(Date))'}>} Processed))

• ###### Re: Excel formula equivalent in a script?

I am trying to get the values for a table. Just the expression doesn't give me what I want. Looking for a way to add this into my table LOAD in the script.

• ###### Re: Excel formula equivalent in a script?

My reply is like Pradosh's.

Maybe something like this

Temp:

noconcatenate

Load

Date,

Area,

Program,

if(Date=peek('Date') and Area = peek('Area') and Program = peek('Program'),

Rangesum(WIP,Processed,peek('WIP'))) as [WIP Inbound],

Processed,

WIP

resident YourTable

order by Area,Program,Date;

drop YourTable;

rename Temp to Yo

• ###### Re: Excel formula equivalent in a script?

can you try this

1:sort say table A by Area,date such that area 'a' with all dates comes first then 'b' then the next area and so on.

2:use the above table as resident load or preceding load and add an extra column "yesterday's WIP" useing peek('WIP',ROWNO()-1,'A')

3: Then calculate the new column (WIP-(yesterday's WIP-Processed) as the new column "WIP inbound"

regards

Pradosh