Concatenate Month and Year fields - not as text

[blunckc1]

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

[stigchel · Accepted solution]

Something like this

date(MakeDate([Fiscal Year],Month([End Date]),1), 'MMM-YYYY') AS MonthYear

[bertinabel]

Intenta con lo siguiente:

floor(date(Date#( [Año fiscal] & Mes ([Fecha de finalización]) ,'YYYYMMM'))) AS YearMonth

[miguelbraga]

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

[blunckc1]

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];

[blunckc1]

Disregard - I have worked this out...  Thanks for the help!

[stigchel]

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]

?