Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Creating FY from MM/YYYY without using Calendar

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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,

9 Replies
Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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,

Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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,

MVP & Luminary
MVP & Luminary

Re: Creating FY from MM/YYYY without using Calendar

Hi,

Try like this

LOAD

*,

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

From DataSource;

Regards,

Jagan.

Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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 Smiley Tongue

Jan ☺

Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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

Not applicable

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

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-QQ-BeginQ-End
FY12 Q36/1/20126/30/2012
FY12 Q47/1/20129/30/2012
FY13 Q110/1/201212/31/2012
FY13 Q21/1/20133/31/2013
FY13 Q34/1/20136/30/2013
FY13 Q47/1/20139/30/2013
FY14 Q110/1/201312/31/2013
FY14 Q21/1/20143/31/2014
FY14 Q34/1/20146/30/2014
FY14 Q47/1/20149/30/2014
FY15 Q110/1/201412/31/2014
FY15 Q21/1/20153/31/2015
FY15 Q34/1/20156/30/2015
FY15 Q47/1/20159/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;

MVP & Luminary
MVP & Luminary

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

Hi,

Use this for getting the FY Quarter

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

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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!

Not applicable

Re: Creating FY from MM/YYYY without using Calendar

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