Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
jjking58
Contributor III
Contributor III

Fiscal Calendar

Please advise,

I created a fiscal calendar using the code below. Over the file listed below. They look to be attached at IHIDAT, but none of the other fields in the table seem to be connected to the calendar . When I make fiscal year selections the  other fields in the file they do not change.

Thanks,

Jim

Cap1.PNG

Cap1.PNG

Cap1.PNG

15 Replies
sunny_talwar

May be IHIDAT is a timestamp?

Try this

LOAD Date(Floor(IHIDAT)) as IHIDAT,

           ....

SQL SELECT *

FROM ....

or IHIDAT is not read as date by QlikView and you might need to use Date#() function to help QlikView understand that it is date

jjking58
Contributor III
Contributor III
Author

Where should i insert this code and the table level or calendar

jjking58
Contributor III
Contributor III
Author

Here are the results prior to implementing your suggestion

Cap1.PNG

sunny_talwar

Can you post your script as text

jjking58
Contributor III
Contributor III
Author

Here is the code.

I have the calendar under a separate tab called dates (not sure if that makes a difference)

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

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

MasterCalendar:

Load distinct

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

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

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

   Ceil(([IHIDAT]-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#(IHIDAT,'YYYYMMDD'),'M/D/YYYY') as IHIDAT,

   YearStart([IHIDAT],0,$(vFM)) as FYearStart,

   Year(Date(Date#(IHIDAT,'YYYYMMDD'),'M/D/YYYY'))               as Year,

   Month(Date(Date#(IHIDAT,'YYYYMMDD'),'M/D/YYYY'))              as Month,

   Date(Monthstart([IHIDAT]), 'MMM-YYYY') as MonthYear,

   Week([IHIDAT])               as ISOWeekNo,

   Dual(WeekDay([IHIDAT]),Mod(WeekDay([IHIDAT]-$(vFD)),7)+1) as WeekDay,

   Day([IHIDAT])                as Day,

   Date([IHIDAT], 'MM/DD')      as DATEMMDD

Resident InvHeader;

InvHeader:

LOAD IHINVN as Inv_Number,

    IHTYPP,

    IHINYY,

    IHORNO,

    IHIDAT,

    IHCUNO as Customer,

    IHICNO,

    IHTOPC,

    IHTOPT,

    IHTDP1,

    IHTODC,

    IHTODD,

    IHMOTC,

    IHMOTT,

    IHCORE,

    IHSALE,

    IHPCUR;

SQL SELECT *

FROM S1018B8M.HB2320BFUS.SROISH WHERE IHINYY IN(2014,2015,2016);

sunny_talwar

Try this:

InvHeader:

LOAD IHINVN as Inv_Number,

    IHTYPP,

    IHINYY,

    IHORNO,

   Date(Floor(Date#(IHIDAT, 'YYYYMMDD'))) as IHIDAT,

    IHCUNO as Customer,

    IHICNO,

    IHTOPC,

    IHTOPT,

    IHTDP1,

    IHTODC,

    IHTODD,

    IHMOTC,

    IHMOTT,

    IHCORE,

    IHSALE,

    IHPCUR;

SQL SELECT *

FROM S1018B8M.HB2320BFUS.SROISH WHERE IHINYY IN(2014,2015,2016);

jjking58
Contributor III
Contributor III
Author

That did it. Can you tell me how i would code a set analysis expression to show the previous year in another chart at the same time.

sunny_talwar

Is Year one of the dimensions?

Also, I suggest creating multiple threads for multiple requirements. This help keep threads focused on one and only one thing which makes it easier for others to see in the future

jjking58
Contributor III
Contributor III
Author

Sorry for mixing threads, i'll separate them in the future.

I've attached my dimensions and a sample of the table i'm trying to generate (price,cost,GP are calculated using set analysis)

Cap1.PNG