Accumulation in script

[pascaldijkshoor]

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?

[marcus_sommer · Accepted solution]

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

[pascaldijkshoor]

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.

[sunny_talwar]

Did you tried the modified response from Marcus?

[pascaldijkshoor]

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

Thanks!