Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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
kashyap311996101
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
kashyap311996101
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)

 

kashyap311996101
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