Script logic required

[Kushal_Chawda]

Hi All,

Need to do calculation as per the attached excel. As formula works in circular fashion , I am not sure how to do it in QlikView.

I want to do it in script only

gwassenaar‌ stalwar1‌ swuehl‌

[jonathandienst]

It would be better to describe what you are trying to achieve (and show/explain the Excel formula) and the expected results, rather than requiring people to try and uncover that information to help you. The people providing help here are not Qlik employees (for the most part) and are providing this (free) service voluntarily. If you make it hard to help you, you will be less likely to get useful advice.

[Kushal_Chawda]

Hi Jonathan,

Thanks for the reply. I am not sure how I will be able to explain the formula here which will be difficult to explain, but in excel there is already formula applied and required output is there. I thought this will be easy instead. Sorry for the inconvenience.

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD RowNo() as SNo,

  Date(Date) as Date,

    Time([Release Time]) as [Release Time],

    [Release Qty],

    [Progressive release],

    Delivery,

    Time([Portal Required Time]) as [Portal Required Time],

    [Portal Required Qty],

    If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    Delivery)) as [Delivery R],

    RangeSum(

    If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    Delivery)), Alt(Peek('Prog Del R'), 0)) as [Prog Del R],

    If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)) as [To be Deliver],

    RangeSum(

    If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

    Delivery)), Alt(Peek('Prog Del R'), 0), -[Progressive release]) as [AB]

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where Len(Trim(Date)) > 0;