Fiscal Calendar

[jjking58]

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

[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]

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

[jjking58]

Here are the results prior to implementing your suggestion

[sunny_talwar]

Can you post your script as text

[jjking58]

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]

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]

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)