Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator III

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

KPI_0043.jpg

Thanks!

5 Replies
Anil_Babu_Samineni

May be this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
joshrussin
Creator III
Creator III
Author

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.

pradosh_thakur
Master II
Master II

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

Learning never stops.
m_woolf
Master II
Master II

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

joshrussin
Creator III
Creator III
Author

I am getting "-" when I use this format. Here is my file.