Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I was able to cumulate monthly sum in load script, but how to make the cumulation restarts for each year in load script?
RangeSum(peek('Cumulative') ,Monthsum) AS Cumulative
I used the code, which calulates 'Monthsum' across all years(left), but what I need is restacking for the begining of each year(right)
The below is the script
RangeSum(peek('Cumulative') ,Monthsum) AS CumulativeCumulatedTable_Temp:
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum
Resident Dat2
group By MonthYear;
TABLE:
LOAD
MonthYear,
Year1,
Month1,
RangeSum(peek('Cumulative') ,Monthsum) AS Cumulative
RESIDENT CumulatedTable_Temp;
Hi. Try this
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum,
If(Year(MonthYear)=Peek(Year1), RangeSum(sum(Unit), Peek(Cumulative)), sum(Unit)) as Cumulative
Resident Dat2
group By MonthYear
Order By MonthYear;
Hope this helps.
Juraj
Hi. Try this
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum,
If(Year(MonthYear)=Peek(Year1), RangeSum(sum(Unit), Peek(Cumulative)), sum(Unit)) as Cumulative
Resident Dat2
group By MonthYear
Order By MonthYear;
Hope this helps.
Juraj
The code caused an error, so I adjusted your code a bit, and which working great. Thank you so much!
CumulatedTable_Temp:
load
MonthYear,
Month(MonthYear) as Month1,
Year(MonthYear) as Year1,
sum(Unit) as Monthsum
Resident Dat2
group By MonthYear;
TABLE:
LOAD
MonthYear,
Year1,
Month1,
If(Year(MonthYear)=Peek(Year1), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative
RESIDENT CumulatedTable_Temp;
Hi. I'm glad you got it working. However, I noticed you omitted "order by" which is important for Peek() to work as expected:
TABLE:
LOAD
MonthYear,
Year1,
Month1,
If(Year(MonthYear)=Peek(Year1), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative
RESIDENT CumulatedTable_Temp
ORDER BY MonthYear
;
I have another question if there's any way I can assign the order of month?
Now, we're stacking Jan, Feb, Mar , Apr.. to Dec. But , let just say, if my fiscal year starts from Mar and finishes to Feb, how can I assign the new order to Rangesum() in load script?
Thank you!
You can use InYear() function (https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunc...)
If(InYear(MonthYear, Peek(MonthYear), 0, 3), RangeSum(Monthsum, Peek('Cumulative')), Monthsum) as Cumulative