Compare current and previous record

[sudhakar0717]

hello everyone,

please help me out for calculating the CalCustomer field given in the attach file.

i have written this  code but its not working in my way

output is also attach.

Base:

LOAD

    Region,

    "Year",

    Customers

FROM [lib://abc.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left join(Base)

Load

Region,

"Year",

if([Region]=Previous([Region]),(Customers+previous(Customers)),Customers)as CalCustomers

Resident Base

order by Region,"Year";

Exit Script;

[OmarBenSalem]

Please refer to this :

QlikView Peek Function

[OmarBenSalem]

you can also achieve this by directly calculating it int table, with :

rangesum(above(sum(customer),0,rowno()))

[micheledenardi]

Hope it helps:

LOAD Region,
    Year,
    Customers,
    Cal_Cust,   
    if(Region=peek(Region),Customers+peek(CalCustomers),Customers) as CalCustomers
FROM

(ooxml, embedded labels, table is Sheet1);

[sudhakar0717]

thank you micheledenardi.

[micheledenardi]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.