Sorting Issue

[nihhalmca]

Hi All -

Need to sort Period column from load script. Period column is not in data format.

Period start from Aug to Jul.

Format: AUG-FY26, SEP-FY26, OCT-FY26 - - - - - JUL-FY26.

Should display period column in Pivot table as below:

Q1-FY26                                          |    Q2-FY26

OCT-FY26, SEP-FY26, AUG-26         JAN-FY26, DEC-FY26, NOV-FY26,

Please assist on this.

Thank you. 

[Daniel_Castella · Accepted solution]

Hi

Could you, please, try the code below? Note that you will need to modify YourDataTable table and Dates field since here I loaded it from an excel as data sample. You will need to write your original table and field.

SET vFiscalYearStartMonth = 8; // Change this to match your fiscal year start month

YourDataTable:
LOAD
MakeDate('20'&Right(Dates,2),Pick(
Match(Left(Dates,3), 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'),
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)) as DateField
FROM [lib://DataFiles/Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Step 1: Get min and max dates from your data
MinMaxDates:
LOAD
Min(DateField) as MinDate,
Max(DateField) as MaxDate
RESIDENT YourDataTable;

LET vMinDate = Peek('MinDate', 0, 'MinMaxDates');
LET vMaxDate = Peek('MaxDate', 0, 'MinMaxDates');
DROP TABLE MinMaxDates;

// Step 2: Generate calendar dates
TempCalendar:
LOAD
Date($(vMinDate) + IterNo() - 1) as TempDate
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

// Step 3: Create the master fiscal calendar
FiscalCalendar:
LOAD
// Fiscal Year logic
Upper(Month(AddMonths(TempDate, $(vFiscalYearStartMonth) - 13))) &
'-FY' & Right(Year(AddMonths(TempDate, $(vFiscalYearStartMonth) - 13)),2) as FiscalMonthYear,
'Q' & Ceil(Month(TempDate)/3) &'-FY'& (Right(Year(TempDate),2)) as FiscalCalendarQuarter
RESIDENT TempCalendar;

DROP TABLE TempCalendar,YourDataTable;

Kind Regards

Daniel

[nihhalmca]

Thanks Daniel.