Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

Accumulation in script

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?

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

4 Replies
marcus_sommer

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
Creator
Creator
Author

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
Creator
Creator
Author

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

Thanks!