topic Re: Creating FY from MM/YYYY without using Calendar in QlikView

Creating FY from MM/YYYY without using Calendar

Tue, 22 Apr 2014 23:06:01 GMT

Hello All - new to QlikView, but not to Access and Excel;

I have 3 columns of data loaded from one field as follows: (original data is YYYYMMDD)

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM/DD/YYYY')as DateLeadSubmitted, (Yield 11/09/2010)

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'MM') as MonthSubmitted, (Yield 11)

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY') as YearSubmitted, (Yield 2010)

Now I need to create a FYSubmitted field which adds 1 to the year when the month is >9; this is what I've been working on, but many variations later, I still get nothing but a dash but oddly enough the script doesn't blow up: (Desired 2011)

If (Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY') > 09, Date(Date#([Date Lead Submitted],'YYYYMMDD

+1'),'YYYY'), Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')) as FYSubmitted,

any takers?

Jan

Re: Creating FY from MM/YYYY without using Calendar

Tue, 22 Apr 2014 23:19:42 GMT

Hi Jan

Check This

If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 09,

Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,

Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))

)

, 'YYYY'),

Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')) as FYSubmitted,

Re: Creating FY from MM/YYYY without using Calendar

Tue, 22 Apr 2014 23:23:18 GMT

Sorry

Us this version please, the last has an error

If (Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))) > 9,

Date(MakeDate( Year(Date#([Date Lead Submitted],'YYYYMMDD'))+1,

Num(Month(Date#([Date Lead Submitted],'YYYYMMDD'))),

Num(Day(Date#([Date Lead Submitted],'YYYYMMDD')))

)

, 'YYYY')

,Date(Date#([Date Lead Submitted],'YYYYMMDD'),'YYYY')

) as FYSubmitted,

Re: Creating FY from MM/YYYY without using Calendar

jagan — Wed, 23 Apr 2014 02:27:06 GMT

Hi,

Try like this

LOAD

Year(YearStart(Date(Date#([Date Lead Submitted], 'YYYYMMDD')), 0, 10)) + 1 AS Year

From DataSource;

Regards,

Jagan.

Re: Creating FY from MM/YYYY without using Calendar

Wed, 23 Apr 2014 13:33:05 GMT

Thank you!!! One wonders why it needs to be so convoluted…?’’I’m grateful to the “plug and play” coding, thank you, but I pride myself in being able to read formulas fairly easily, but this one throws me for a loop 😛

Jan ☺

Re: Creating FY from MM/YYYY without using Calendar

Wed, 23 Apr 2014 13:38:41 GMT

Thank you!!! One wonders why it needs to be so convoluted…?’’I’m grateful to the “plug and play” coding, thank you again; I pride myself in being able to read formulas fairly easily, but this one throws me for a loop

Jan

Re: Re: Creating FY from MM/YYYY without using Calendar

Wed, 23 Apr 2014 20:26:44 GMT

OK, I'm going to push my luck - the FY above works marvelously, and I placed part of that formula into my calendar and can successfully get a correct FY - but now I'm stuck on FQ

here is an excerpt from my Calendar script: AND IT WORKS THANK YOU MARCOS!!!

If (Num(Month(Date#([TempDate]))) > 09,

Date(MakeDate( Year(Date#([TempDate]))+1,Num(Month(Date#([TempDate]))),

Num(Day(Date#([TempDate])))), 'YYYY'),

Date(Date#([TempDate]),'YYYY')) as FYYear,

what I now need is to get the Fiscal Quarter to skew so that 10/1 starts Q1 of the next fiscal year and I think this why everyone uses a calendar:

(but I can't make the simple code provided HERE to work . I've pasted my calendar code at the bottom of this page in case some need to see it in its entirety. The SET vFM = 10 is on the Main Load Page with all the other variables)

10/01/2014 EQUALS FY15 Q1

I've been playing with this:

'Q' & ceil((month(TempDate)/3)-3) as FYQuarter,

but that renders negative numbers and doesn't recycle back to 1

I have a lookup table that I am currently using for an excel spreadsheet, can I somehow utilize that?:

FY-Q	Q-Begin	Q-End
FY12 Q3	6/1/2012	6/30/2012
FY12 Q4	7/1/2012	9/30/2012
FY13 Q1	10/1/2012	12/31/2012
FY13 Q2	1/1/2013	3/31/2013
FY13 Q3	4/1/2013	6/30/2013
FY13 Q4	7/1/2013	9/30/2013
FY14 Q1	10/1/2013	12/31/2013
FY14 Q2	1/1/2014	3/31/2014
FY14 Q3	4/1/2014	6/30/2014
FY14 Q4	7/1/2014	9/30/2014
FY15 Q1	10/1/2014	12/31/2014
FY15 Q2	1/1/2015	3/31/2015
FY15 Q3	4/1/2015	6/30/2015
FY15 Q4	7/1/2015	9/30/2015

~ ~ ~ MasterCalendarCode ~ ~ ~

MinMax:

LOAD

Min(DateLeadSubmitted) as MinDate,

Max(DateLeadSubmitted) as MaxDate

RESIDENT FSA_BaseDataSAP;

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

// ********* Temporary Calendar *********

TempCal:

LOAD

date($(vMinDate)+rowno()-1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate)+1;

DROP TABLE MinMax;

// ********* Master Calendar *********

MasterCalendar:

LOAD

TempDate as Date,

// Week(TempDate) as Week,

Year(TempDate) as Year,

If (Num(Month(Date#([TempDate]))) > 09,

Date(MakeDate( Year(Date#([TempDate]))+1,Num(Month(Date#([TempDate]))),

Num(Day(Date#([TempDate])))), 'YYYY'),

Date(Date#([TempDate]),'YYYY')) as FYYear,

Date(monthstart(TempDate),'MM') as Month,

// Month(TempDate) as Month,

Day(TempDate) as Day,

// Weekday(TempDate) as WeekDay,

'Q' & ceil(month(TempDate)/3) as Quarter,

'Q' & ceil((month(TempDate)/3)-3) as FYQuarter,

Date(monthstart(TempDate),'MM-YYYY') as MonthYear,

// Week(TempDate)&'-'&Year(TempDate) as WeekYear,

inyeartodate(TempDate,$(vToday),0)*-1 as CurYTDFlag,

inyeartodate(TempDate,$(vToday),-1)*-1 as LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: Re: Creating FY from MM/YYYY without using Calendar

jagan — Thu, 24 Apr 2014 01:50:17 GMT

Hi,

Use this for getting the FY Quarter

'Q' & Pick(ceil((month(TempDate)/3)), 2, 3,4,1)

Hope this helps you.

Regards,

Jagan.

Re: Creating FY from MM/YYYY without using Calendar

Tue, 27 May 2014 17:42:31 GMT

Janet,

At most custoemr sites, just like your current place of work, people use Time Dimension Tables from the database as a better solution than writing code to convert Calendar date or Julian Date to Fiscal Date. For example at our employer, we have a database table called Time_Dim which you can join to the said date field (Lead date...?) and the time dim table record may have many columns called FY, FQ, Fperiod, Week #, Day number, etc., to the point that you can get any specific date field incldung day names, exclude/include holdiays etc.,

that method is 100 times less work and more accurate (because the script examples below have to be done for each date column!)

Time Dim table is created one time for subject date range (say 1960 to 2099) and saved and you dont have to relead that QVD multiple times., Just join on the Date field. We do this at Siemens!

Re: Creating FY from MM/YYYY without using Calendar

Wed, 28 May 2014 12:51:56 GMT

Hi Suresh,

I was told we didn't have ANY coding at all - but I love to be wrong, especially in this case - thanks for the table info and I'll be sure to grab it once I have access to it 😉 Can't wait to work with you FT on this!

Jan