topic Re: Accumulation in script in App Development

Accumulation in script

pascaldijkshoor — Fri, 15 Jun 2018 10:28:28 GMT

Hello Everyone,

I want to add an accumulated field in my script. The field must accumulate the total driven KMs per asset number per month. I have tried the following script:

ExcelTable:

LOAD

"Date",

"Asset Nr" as Asset_Number,

"Driven Kilometers",

FROM [lib://Data_Qlik /Qlik Kilometers 2.xlsx]

(ooxml, embedded labels, table is All);

NoConcatenate

Newtable:

LOAD *,

if (Previous(Asset_Number)= Asset_Number, if (Previous(Month("Date"))= Month("Date"),rangesum (peek("Driven Kilometers"))+"Driven Kilometers", "Driven Kilometers")) as Accumulated_KMs

Resident ExcelTable

Order By Asset_Number,"Date";

This gives me the sum of yesterday's driven KMs and today's driven KMs, but it does not sum the whole month's driven KMs.

How do I need to adjust the script?

Re: Accumulation in script

marcus_sommer — Fri, 15 Jun 2018 10:46:37 GMT

Try it with:

Newtable:

LOAD *,

if (Previous(Asset_Number)= Asset_Number,

if (Previous(Month("Date"))= Month("Date"),

rangesum (peek("Accumulated_KMs")), "Driven Kilometers"), "Driven Kilometers"))

as Accumulated_KMs

Resident ExcelTable

Order By Asset_Number,"Date";

Edit: the second bracket by peek is wrong and needs to be removed and peek needs single-quotes for the fieldname:

rangesum (peek('Accumulated_KMs'), "Driven Kilometers"), "Driven Kilometers"))

- Marcus

Re: Accumulation in script

pascaldijkshoor — Fri, 15 Jun 2018 11:11:22 GMT

This does not work correct. Now the Accumulated_KMs field shows for every month only the Driven KMs of the first day of the month.

Re: Accumulation in script

sunny_talwar — Fri, 15 Jun 2018 11:20:07 GMT

Did you tried the modified response from Marcus?

Re: Accumulation in script

pascaldijkshoor — Fri, 15 Jun 2018 11:31:42 GMT

Sorry I didn't copy your script right, now it works

Thanks!