Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi. Does anyone have a good way of summing the last 12months in the load script? Se excel sheet
There is one value per monthKey. "Value". I want to add another value per month called "Test" i the excel sheet which is the sum of the last 12 months. This has to be in the load script and not in a tabel/graph etc.
I tried Rangesum() but I didnt seem to get that right.
Thank you
May be something like this
LOAD *,
If(Name = Peek('Name', -12),
RangeSum(Peek('Value', -1), Peek('Value', -2), Peek('Value', -3), Peek('Value', -4), Peek('Value', -5), Peek('Value', -6), Peek('Value', -7), Peek('Value', -8), Peek('Value', -9), Peek('Value', -10), Peek('Value', -11), Peek('Value', -12)))
Resident ....
Order By Name, Date;
What was the script that you have tried thus far?
RangeSum(Verdi, Peek(%MånedKEY), -(%MånedKEY-12)) as Test, not correct
You will have to peek Test to accumulate
RangeSum(Verdi, Peek('Test'), -(%MånedKEY-12)) as Test
But since you have not provided any other part of the script, I am not sure if we need anything else here or not
Hi. Not much to the script as it is just a month column, a value column. Looks like this:
test:
LOAD %MånedKEY,
RangeSum(Verdi, Peek('Test'), -(%MånedKEY-12)) as Test,
%ID,
Type,
Verdi,
Name,
Enhet
FROM
grain is Month and name.
Sorry should have included that
So you want for each name the rolling 12 month sum?
Yes Sorry my bad for not giving all info......
May be something like this
LOAD *,
If(Name = Peek('Name', -12),
RangeSum(Peek('Value', -1), Peek('Value', -2), Peek('Value', -3), Peek('Value', -4), Peek('Value', -5), Peek('Value', -6), Peek('Value', -7), Peek('Value', -8), Peek('Value', -9), Peek('Value', -10), Peek('Value', -11), Peek('Value', -12)))
Resident ....
Order By Name, Date;
Yes I think that works. I have to check more tomorrow. THANK YOU