How to create fiscal calendar in Incorta?(Part 1)

I will refer to the fiscal calendar from the National Retail Federation and create the same fiscal calendar in Incorta.

https://nrf.com/resources/4-5-4-calendar

First, I created a materialized view using PySpark in Incorta. You can click this link to see how to use PySpark to create a materialized view in Incorta. https://community.incorta.com/t/x1jhfc/creating-materialized-view-using-pyspark

In the python part, I generated the fiscal year according to the number of days within a year.  In a week based fiscal calendar, a year can have either 52 or 53 weeks. I use the number of weeks and the days of year to determine the start of years.

	%pyspark
	from pyspark.sql import *
	from pyspark.sql.types import *
	from datetime import date,timedelta
	import holidays
	from pyspark.sql.functions import *
	from pyspark.sql.window import Window
	#please prove the start date of any fiscal year.
	fiscal_start= '2017-01-29'
	#please prove the list 53 week year.
	list_of_53_week_year= [2017,2025]
	start_year= 2016
	date_start=date(2016,01,31)
	date_end=date(2020,03,31)
	delta=date_end-date_start
	cData=[]
	i=0
	y=start_year
	doy=1
	month_pattern='4-5-4'
	while i <= delta.days:
	r=[]
	r.append(i)
	r.append(doy)
	r.append(y)
	cData.append(r)
	i += 1
	doy += 1
	if ( y in list_of_53_week_year) and (doy > 53*7):
	doy=1
	y+=1
	elif (not y in list_of_53_week_year) and (doy > 52*7):
	doy=1
	y+=1
	cSchema = StructType([StructField("idx", IntegerType()),StructField("doy", IntegerType()), StructField("fiscal_year", IntegerType())])
	df_idx = spark.createDataFrame(cData,schema=cSchema)
	df_idx.registerTempTable("TBL")

view raw Fiscal_date(Part1) hosted with ❤ by GitHub

This is the first part of the fiscal calendar. It is now a dataframe with three columns. the 'idx' column with the sequence number, 'doy' column with the day of the years, and 'fiscal year' column. 

In the second part, I will explain how to get the data we need such as fiscal_week_number, fiscal_year_start_date, fiscal_week_of_year, etc. Please click this link. https://suziepyspark.blogspot.com/2020/11/how-to-create-fiscal-calendar-in_30.html