Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ];
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!
@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.
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().
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!