how to create the fiscal calender with existing data which contains only FISCAL Period ID no Date input

[Karim_Khan]

how to create the fiscal calender with existing data which contains only FISCAL Period ID no Date input

[jagan]

Hi Karim,

What values are there in Fiscal Period ID? How it is derived?  What is the logic for that, is there any logic to derive the date field.

Attach some sample data and required output for this.

Regards,

Jagan.

[Karim_Khan]

Hi Sir,

  I am trying to get the data from excel sheet in which only FISCAL Period ID is given like

201506,

201507,

201411

I have applied the below logic

Cisco:

LOAD [End Customer Global Ultimate Name],

     Country,

     [End Customer HQ Top],

     [Sales Level 1],

     [Sales Level 2],

     [Sales Level 3],

     [Sales Level 4],

     [Sales Level 5],

     [Partner Name],

     [Fiscal Period ID],

     Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,

    Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,

     Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,

     FMV,

     [Net Bookings],

     Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS CiscoYear, // Standard Calendar Year

Month(Date) AS CiscoMonth, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,

Date(Num(Date#([Date],'YYYYMM')),'DD-MM-YYYY') as FDate; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Values are getting calculated but fields are not properly associated If I select the FiscalDate then values get de-associated and wrong calculations shows.But If I select the Fiscal Period ID then Correct Calculation is showing

Regards,

K K

[jagan]

Hi,

I think the date formats are different in both the tables,

Try changing this line in your master calendar script

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1, 'DD-MM-YYYY') AS Date,

Hope this helps you.

Regards,

Jagan.

[Karim_Khan]

Still Same problem

[jagan]

Can you attach the sample Excel file with some data so that it would be easier to solve this.

Regards,

Jagan.

[jagan]

Hi Karim,

Try this script

Cisco:

LOAD [End Customer Global Ultimate Name],

     Country,

     [End Customer HQ Top],

     [Sales Level 1],

     [Sales Level 2],

     [Sales Level 3],

     [Sales Level 4],

     [Sales Level 5],

     [Partner Name],

     [Fiscal Period ID],

     Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,

    Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,

     Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,

     FMV,

     [Net Bookings],

     Group

FROM

[Sample_data.xlsx]

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS CiscoYear, // Standard Calendar Year

Month(Date) AS CiscoMonth, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,

Date(Date#([Date],'DD-MM-YYYY'),'DD-MM-YYYY') as FDate; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1, 'DD-MM-YYYY') AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Regards,

Jagan.

[Karim_Khan]

Sir,

It is showing the same calculation.But I want calculation on the basis of Month Selection not Fiscal PeriodID selection.

Regards,

K K

[jagan]

Hi,

Can you come up with an example what exactly you are trying to do?

Or check this

Cisco:

LOAD [End Customer Global Ultimate Name],

     Country,

     [End Customer HQ Top],

     [Sales Level 1],

     [Sales Level 2],

     [Sales Level 3],

     [Sales Level 4],

     [Sales Level 5],

     [Partner Name],

     [Fiscal Period ID],

     Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,

    Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,

     Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,

     FMV,

     [Net Bookings],

     Group

FROM

[Sample_data.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS CiscoYear, // Standard Calendar Year

Month(Date) AS CiscoMonth, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Month(Date) AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date) AS FiscalYear,

Date(Date#([Date],'DD-MM-YYYY'),'DD-MM-YYYY') as FDate; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1, 'DD-MM-YYYY') AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Regards,

Jagan.