Cumulated sum table for fiscal year starting from Oct in load editor

[nezuko_kamado]

How can I create an accumulated table starting from Oct in load editor?(I know how to create it in a chart, but i want to create it in load editor)

I tried this code

finalData: 
Load *, 
RangeSum(Value,Peek(CumulativeSum)) as CumulativeSum 
Resident test 
order by FiscalYear, Month;

 But the fiscal year should start from Oct and finishe to Sep, instead of strating from Jan.

So I had changed the main setting starting with Oct as below

SET MonthNames='Oct;Nov;Dec;Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep';
SET LongMonthNames='October;November;December;January;February;March;April;May;June;July;August;September';

then which caused whole confusion over other analysis pages in the  app that require counting Jan first, and  I can't use it.

How can I create a cumulative table with the monthly order of Oct, Nov, Dec, Jan, Feb,....., Aug, Sep, and

restart cumulating each fiscal year like below?

test: Load * Inline [ Month,Year, Value, FiscalYear Oct, 2018, 10, 19 Nov, 2018, 11, 19 Dec, 2018, 12, 19 Jan, 2019, 11, 19 Feb, 2019, 9, 19 Mar, 2019, 10, 19 Apr, 2019, 8, 19 May, 2019, 13, 19 Jun, 2019, 10, 19 Jul, 2019, 11, 19 Aug, 2019, 12, 19 Sep, 2019, 9, 19 Oct, 2019, 9, 20 Nov, 2019, 14, 20 Dec, 2019, 11, 20 Jan, 2020, 10, 20 Feb, 2020, 11, 20 Mar, 2020, 9, 20 Apr, 2020, 8, 20 May, 2020, 12, 20 Jun, 2020, 11, 20 Jul, 2020, 10, 20 Aug, 2020, 10, 20 Sep, 2020, 11, 20 ]; 

[sidhiq91]

@nezuko_kamado  Did you get a chance to go through the below mentioned Article regarding the fiscal year.

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

I think this should resolve you issue, please go through it. 

[nezuko_kamado]

Thank you, but I don't think you read through my question, or I asked it the wrogn way. This is not a question of how to set Fiscal year.

I did created Fiscal year and no issue to use it. But my app has to use both Fiscal year and Calendar year, and I can't switch all date setting to Fiscal year.  Because of that, when I use Peek() for Rangesum(), Peek() chooses January first instead of October. 

So is there any function that changes or re-assign the order of month, and can be used with Peek() or Rangesum().  

[nezuko_kamado · Accepted solution]

First, I created new order of month, 'FiscalYearOrder' using addMonths()

Next, I created another variable, 'NewOrder' which combines 'Fiscal Yea'r and the new variable 'FiscalYearOrder'

Then, used Peek() that allows  'NewOrder' restarts every new Fiscal year.

Right Join(test)
Load*,
Num(Month(AddMonths(Date#(Month,'MMM'),3)),00)as  FiscalYearOrder
Resident test;


RIGHT JOIN(test)
LOAD
	*,
    num(FiscalYear)&num(FiscalYearOrder, '00') as NewOrder
    RESIDENT test;


Cumulative:
Load
*,
If(FiscalYear=left(peek(NewOrder),2), rangesum(Value, Peek('Cumulative')),Value) as Cumulative
Resident test
order by FiscalYear, NewOrder;

then yay!