Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to create a MonthYear field for a fiscal calendar so I need to concatenate two fields together. However when I do this, it turns into text so I can't use this for a time period chart.
Can't work out for how to concatenate my fields and maintain them as date fields. I need to do this so a line chart can be built on the fiscal MonthYear. The Fiscal Year is not the year of the End Date or the Start.
Start Date | End Date | Pay Date | Period # | Pay Period # | Fiscal Year |
22/06/2015 | 5/07/2015 | 15/07/2015 | 1 | PP1/2016 | 2016 |
Here is my script with the two rows highlighted that need to be concatenated together.
//Load pay period data
PayPeriodCalendar:
LOAD [Start Date],
[End Date],
[Pay Date],
[Pay Period #],
[Fiscal Year],
Month([End Date]) as [Fiscal Month],
Date([Start Date]+iterno()-1) as ReferenceDate
FROM
MappingTables\PayPeriodCalendar.xls
(biff, embedded labels, table is Sheet1$) While [Start Date]+IterNo()-1 <=[End Date];
Thank you
Carl
Something like this
date(MakeDate([Fiscal Year],Month([End Date]),1), 'MMM-YYYY') AS MonthYear
Something like this
date(MakeDate([Fiscal Year],Month([End Date]),1), 'MMM-YYYY') AS MonthYear
Intenta con lo siguiente:
floor(date(Date#( [Año fiscal] & Mes ([Fecha de finalización]) ,'YYYYMMM'))) AS YearMonth
Try in script something like this:
//Load pay period data
PayPeriodCalendar:
LOAD *,
date(
makedate(
[Fiscal Year],
[Fiscal Month],
1
),
'MMM YYYY') as Month_Year;
LOAD [Start Date],
[End Date],
[Pay Date],
[Pay Period #],
[Fiscal Year],
Month(
date(
date#([End Date], 'DD/MM/YYYY'),
'DD/MM/YYYY')
)
) as [Fiscal Month],
Date([Start Date]+iterno()-1) as ReferenceDate
FROM
MappingTables\PayPeriodCalendar.xls
(biff, embedded labels, table is Sheet1$) While [Start Date]+IterNo()-1 <=[End Date];
Regards,
MB
Hi Piet,
Thank you so much, really appreciate it!
I realised though by doing this January is being recognised as the first month of the year, which is wrong though. So I read a blog from HIC Fiscal Year but I am getting a "Field not found - <Year>" script error and I can't work out why.
Any thoughts as to why? Here is the new script, thank you!
Set vFM= 7 ; //First month of fiscal year
//Load pay period data
FiscalCalendar:
LOAD Dual([Fiscal Year]-1 &'/'& [Fiscal Year], fYear) as FiscalYear,
Dual(Month([End Date]), fMonth) as [Fiscal Month],
*;
Load Year + If([Fiscal Month]>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod([Fiscal Month]-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
PayPeriodCalendar:
Load [Start Date],
[End Date],
[Pay Date],
[Pay Period #],
date(MakeDate([Fiscal Year],Month([End Date]),1), 'MMM-YYYY') AS [Fiscal Month Year],
Date([Start Date]+iterno()-1) as ReferenceDate
FROM
MappingTables\PayPeriodCalendar.xls
(biff, embedded labels, table is Sheet1$) While [Start Date]+IterNo()-1 <=[End Date];
Disregard - I have worked this out... Thanks for the help!
In your first preceding load you try to reference a Field Year which is not in the xls file
Load Year +
Should probably be
[Fiscal Year]
?