Sort Date into Fiscal Year

[Anonymous]

HI all,

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

I have this as my master calendar,

MinMax:
LOAD
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:
LOAD date($(varMinDate)+IterNo()-1) AS 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
          *;
Load Year(Datefield)                  as Year,           // Your standard master calendar
         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

[vinieme12 · Accepted solution]

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:
LOAD
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:
LOAD date($(varMinDate)+IterNo()-1) AS 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
          *;
Load

          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//////////////////

[vinieme12]

jiasheng‌ if your query is resolved please close the thread!

Qlik Community Tip: Marking Replies as Correct or Helpful