topic Re: Excel formula equivalent in a script? in QlikView

Excel formula equivalent in a script?

joshrussin — Fri, 13 Oct 2017 21:53:18 GMT

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?

Anil_Babu_Samineni — Sat, 14 Oct 2017 02:42:57 GMT

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?

joshrussin — Mon, 16 Oct 2017 14:44:02 GMT

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?

pradosh_thakur — Mon, 16 Oct 2017 18:26:31 GMT

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

Re: Excel formula equivalent in a script?

m_woolf — Mon, 16 Oct 2017 18:52:14 GMT

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?

joshrussin — Wed, 18 Oct 2017 20:37:36 GMT

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