Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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.