Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Sort Date into Fiscal Year

HI all,

I wanna sort my transaction date into fiscal, which is 1st April - 31th March.

I have this as my master calendar,

MinMax:
Max([A Awardered Date]) as MaxDate,
Min([A Awardered Date]) as MinDate
RESIDENT Fact;

LET varMinDate = num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
LET vToday = \$(vMaxDate);

Datefield:
AUTOGENERATE (1)
WHILE \$(varMinDate)+IterNo()-1<= \$(varMaxDate);

Set vFM = 4 ;                                                          // First month of financial year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
Dual(Month, fMonth)              as FMonth,            // Dual financial month
*;
Load Year + If(Month>=\$(vFM), 1, 0)  as fYear,         // Numeric financial year,
Mod(Month-\$(vFM), 12)+1      as fMonth,         // Numeric financial month
*;
Month(Datefield)             as Month,

Datefield
Resident Datefield;
DROP Table Datefield;

What happen is that my record are duplicated and different fiscal year.

As shown comparing with FYear and without. I match it with different colour for easier comparison. What would have cause this and how to I fix it?

Regards,

Frederic

1 Solution

Accepted Solutions
Champion III

Create a FiscalYearMonth Field and sort by Expression FiscalYearMonth

,

how is your calendar field linked to the your Fact table?? i don;t see the date field that should be linked to your Fact, which is causing  a cartesian join

////////////////////HOW IT SHOULD BE////////////////////////

MinMax:
Max([A Awardered Date]) as MaxDate,
Min([A Awardered Date]) as MinDate
RESIDENT Fact;

LET varMinDate = num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
LET vToday = \$(vMaxDate);

Datefield:
AUTOGENERATE (1)
WHILE \$(varMinDate)+IterNo()-1<= \$(varMaxDate);

Set vFM = 4 ;                                                          // First month of financial year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
Dual(Month, fMonth)              as FMonth,            // Dual financial month
*;
Load Year + If(Month>=\$(vFM), 1, 0)  as fYear,         // Numeric financial year,
Mod(Month-\$(vFM), 12)+1      as fMonth,         // Numeric financial month
*;

Datefield as [A Awardered Date],

Year(Datefield)                  as Year,           // Your standard master calendar
Month(Datefield)             as Month,

Datefield
Resident Datefield;
DROP Table Datefield;

////////////////////end of script//////////////////

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
2 Replies
Champion III

Create a FiscalYearMonth Field and sort by Expression FiscalYearMonth

,

how is your calendar field linked to the your Fact table?? i don;t see the date field that should be linked to your Fact, which is causing  a cartesian join

////////////////////HOW IT SHOULD BE////////////////////////

MinMax:
Max([A Awardered Date]) as MaxDate,
Min([A Awardered Date]) as MinDate
RESIDENT Fact;

LET varMinDate = num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
LET vToday = \$(vMaxDate);

Datefield:
AUTOGENERATE (1)
WHILE \$(varMinDate)+IterNo()-1<= \$(varMaxDate);

Set vFM = 4 ;                                                          // First month of financial year

Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
Dual(Month, fMonth)              as FMonth,            // Dual financial month
*;
Load Year + If(Month>=\$(vFM), 1, 0)  as fYear,         // Numeric financial year,
Mod(Month-\$(vFM), 12)+1      as fMonth,         // Numeric financial month
*;

Datefield as [A Awardered Date],

Year(Datefield)                  as Year,           // Your standard master calendar
Month(Datefield)             as Month,

Datefield
Resident Datefield;
DROP Table Datefield;

////////////////////end of script//////////////////

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Champion III