Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
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,
Hi,
Try like this
LOAD
*,
Year(YearStart(Date(Date#([Date Lead Submitted], 'YYYYMMDD')), 0, 10)) + 1 AS Year
From DataSource;
Regards,
Jagan.
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 ☺
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
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;
Hi,
Use this for getting the FY Quarter
'Q' & Pick(ceil((month(TempDate)/3)), 2, 3,4,1)
Hope this helps you.
Regards,
Jagan.
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!
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