Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jjking58
Contributor III
Contributor III

Date filtering by fiscal month

Hi,

I have a fiscal calendar setup (see below) which displays APR-MAR.  When I select December the Voucher Date filters my table

(GLTransaction) by month 12. Can someone show me how to have Voucher Date filter by fiscal period month 9.

Thanks in advance   

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

Set vFD = 6;     // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

Set vFyear = ' ';

MasterCalendar:

Load Distinct

   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year

   Dual(Month, fMonth)                as FMonth,  // Dual fiscal month

   Dual(Month, fMonth)& fYear As FMonthYear,

   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

  

   Ceil((VoucherDate-StartOfFWeekOne+1)/7) as FWeekNo,

   *;

Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year

   Mod(Month-$(vFM), 12)+1            as fMonth,  // Numeric fiscal month

   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,

    

  

   *;

Load

   Date(Date#(VoucherDate,'YYYYMM'),'MM/YYYY') as  VoucherDate,

   YearStart(VoucherDate,0,$(vFM)) as FYearStart,

   Year(Date(Date#(VoucherDate,'YYYYMM'),'YYYYMM'))               as Year,

   Month(Date(Date#(VoucherDate,'YYYYMM'),'YYYYMM'))              as Month,

  

  

   Date(Monthstart(VoucherDate), 'MMM-YYYY') as MonthYear,

   Week(VoucherDate)               as ISOWeekNo,

   Dual(WeekDay(VoucherDate),Mod(WeekDay(VoucherDate-$(vFD)),7)+1) as WeekDay,

   Day(VoucherDate)                as Day,

   Date(VoucherDate, 'MM/DD')      as DATEMMDD

Resident GLTransaction;

2 Replies
jwjackso
Specialist III
Specialist III

If your GLTransaction table is based on calendar year and the statement "When I select December the Voucher Date filters my table (GLTransaction) by month 12" means that there is an AccountPeriod on the GLTransaction table.  The results of the selection are what I would expect.

The FMonth 9 does equal calendar period 12.  You can add a Fiscal Period Month filter pane so the user can select FMonth 12 and expect the GLTransaction table to show data for March.

jjking58
Contributor III
Contributor III
Author

Thanks for the reply. I try using the Fiscal Period filter