Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
Creator II

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

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.

Labels (2)
1 Solution

Accepted Solutions
Kashyap_R
Partner - Specialist
Partner - Specialist

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

 

Thanks and Regards
Kashyap.R

View solution in original post

3 Replies
Kashyap_R
Partner - Specialist
Partner - Specialist

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

Thanks and Regards
Kashyap.R
abc_18
Creator II
Creator II
Author

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
Partner - Specialist
Partner - Specialist

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

 

Thanks and Regards
Kashyap.R