Can we create Date Field from Month and Year and also can we generate fiscal calendar based on that.

[abc_18]

Hi,

How do I create a Date Field if i have Data for Month and Year .

I have created a date filed like below:-

Date#(years & '-' & months, 'YYYY-MMM') as Date_Test, but I have to generate fiscal calendar based on this date field.

I tried to link this date field to my master calendar, but for Fyear, Fmonth I am getting blank values.

Script for master cal:

Set vFM = 4 ; // First month of fiscal year
Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(Date_Test) as Year, // Your standard master calendar
Month(Date_Test) as Month,
num(Month(Date_Test)) as monthno,
Date(Monthstart(Date_Test), 'YYYY-MMM') as MonthYear,
Date_Test
Resident XYZ;

Thanks.

[Kashyap_R]

Hi

to create a date you can use this

DateDate#(years & '-' & months, 'YYYY-MMM'),'DD/MM/YYYY') as Date

the output will be given as only 1st day of every month like 01/01/2019,01/02/2019,01/03/2019....etc

for fiscal calendar go through this link

https://community.qlik.com/t5/New-to-QlikView/Different-fiscal-year-from-calender-year/td-p/300654

Hope this helps

Thanks

[abc_18]

Hi Kashyap,

I have already generated date field using below expression:-

Date(MakeDate(years, months, 1), 'MMM-YYYY') as Date, this gives me date as 2010-Jan.

I have linked this date field with master calendar, but when I calculate YTD expression, it's giving me blank values.

I am using below expression to calculate YTD, please guide me where I am doing wrong, I think it's not able to recognize the date format, as format is in 'MMM-YYYY' only.

Sum({<fYear,FMonth,Date={">=$(=YearStart(max(Date),0,4))<$(= addyears(max(Date), 0,4))"}>}qty)

[Kashyap_R · Accepted solution]

Hi

Try to maintain the same date format for both the master calendar date and your table date column.

2010 - Jan is not the date format it is YYYY - MMM format instead you can use the expression that is mentioned in the above comment that I have posted or you can use yours with some changes as below

Date(MakeDate(years, months,01)) as Date

Hope this Helps

Thanks