Creating FY from MM/YYYY without using Calendar

[#1]

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

[#2]

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,

[#3 · Accepted solution]

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,

[jagan]

Hi,

Try like this

LOAD

*,

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

From DataSource;

Regards,

Jagan.

[#5]

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 ☺

[#6]

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

[#7]

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;

[jagan]

Hi,

Use this for getting the FY Quarter

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

Hope this helps you.

Regards,

Jagan.

[#9]

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!

[#10]

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