Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
Did you tried the modified response from Marcus?
Sorry I didn't copy your script right, now it works
Thanks!