topic Re: Fiscal Calendar in QlikView

Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 18:58:42 GMT

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

Re: Fiscal Calendar

sunny_talwar — Fri, 23 Dec 2016 19:12:22 GMT

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

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 19:15:34 GMT

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

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 19:18:08 GMT

Here are the results prior to implementing your suggestion

Re: Fiscal Calendar

sunny_talwar — Fri, 23 Dec 2016 19:19:52 GMT

Can you post your script as text

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 19:26:42 GMT

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);

Re: Fiscal Calendar

sunny_talwar — Fri, 23 Dec 2016 19:35:28 GMT

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);

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 19:54:07 GMT

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.

Re: Fiscal Calendar

sunny_talwar — Fri, 23 Dec 2016 19:56:06 GMT

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

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 20:03:01 GMT

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)

Re: Fiscal Calendar

jjking58 — Fri, 23 Dec 2016 20:59:36 GMT

Sunny,

Were you able to make anything out of the screen shots i sent

Thanks

Jim

Re: Fiscal Calendar

sunny_talwar — Fri, 23 Dec 2016 22:03:03 GMT

What is the expression for current year for which you are looking to get the previous year?

Re: Fiscal Calendar

jayanttibhe — Sat, 24 Dec 2016 01:43:48 GMT

Hi,

Create one more dimension for Year in your script. (Just take first 4 characters from the IHIDAT).

You need to use following expressions for :

Current Year :; Sum({$<Year={$(=max(Year))}>}Price)

Precious Year :: Sum({$<Year={$(=max(Year)-1)}>}Price)

Thanks

Jayant

Re: Fiscal Calendar

jjking58 — Sat, 24 Dec 2016 18:15:53 GMT

Sunny/Jayant,

I already have a "Year" setup in my script( see original request attached above).

Here are my expressions. I placed the previous year expression into the line and show no data displays.

Please advise

Thanks

Sum({<IDPAGR = {'EQUP'},OrderType-={'TI'},Year={$(=max(Year)-1)},NOAGRP-={'INTR'}>} Amount)

Sum({<IDPAGR = {'EQUP'},OrderType-={'TI'},Year={$(=max(Year)-1)},NOAGRP-={'INTR'}>} Cost)

Column(1)-Column(2) = for GP

Re: Fiscal Calendar

jayanttibhe — Sun, 25 Dec 2016 03:25:11 GMT

Well - Difficult to look in without QVW file. Try above expression in text box object and try to trouble shoot only with the year field.

Thanks

Jayant

Re: Fiscal Calendar

jjking58 — Mon, 26 Dec 2016 15:46:26 GMT

Jayant,

I've tried trouble shooting as you suggested and have the same results. I not sure I'm understanding what to do. I created a field in the fiscal calendar call "Year" based on field IHIDAT. When I test do i use Year or IHIDAT (since there both the same field). Please explain this

Thanks

Jim