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: 
Anonymous
Not applicable

Date format in number

Hello,

  This is a easy question:

This is an extract of my MasterCalendar:

MasterCalendar:

Load

TempDate As CalendarDate,

MonthEnd(TempDate) As ReportingDate,

week(TempDate) As CalendarWeek,

WeekDay(TempDate) as CalendarWeekDay,

weekstart(TempDate) As CalendarWeekStart,

Year(TempDate) As CalendarYear,

Month(TempDate) As CalendarMonth,

MonthName(TempDate) As CalendarMonthYear,

Day(TempDate) As CalendarDay,

'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,

Thank you!

10 Replies
Anonymous
Not applicable
Author

Sorry  I've forgotten to  post the  question is : Why do I get the ReportingDate as a number and the Others as Dates?

Thank you!

PrashantSangle

and your question is????

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
shraddha_g
Partner - Master III
Partner - Master III

date(TempDate) As CalendarDate,

PrashantSangle

Hi,

While creating qvd qlik engine convert value into numeber.

So if you want to ReportingDate in date format use date(ReportingDate) as ReportingDate

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anil_Babu_Samineni

I didn't find ReportingDate field from your posting. Can you send full code

Or else

Date(Date#(ReportingDate,'YourDateFormatHere'),'YourRequiredFormatHere') as ReportingDate

OR

Date(Num(ReportingDate),'YourRequiredFormatHere') as ReportingDate

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shraddha_g
Partner - Master III
Partner - Master III

date(MonthEnd(TempDate)) As ReportingDate

Anonymous
Not applicable
Author

Yes, but Why the other date fields were not converted?

Even I indicate ReportingDate as a date(ReportingDate) the engine insists in indicate it as a "Number" date format.

Anonymous
Not applicable
Author

This is the hole code:

Load

TempDate As CalendarDate,

date(MonthEnd(TempDate)) As ReportingDate,

week(TempDate) As CalendarWeek,

WeekDay(TempDate) as CalendarWeekDay,

weekstart(TempDate) As CalendarWeekStart,

Year(TempDate) As CalendarYear,

Month(TempDate) As CalendarMonth,

MonthName(TempDate) As CalendarMonthYear,

Day(TempDate) As CalendarDay,

'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,

Num(Month(TempDate)) as CalendarPeriod,

IF(TempDate>=addmonths(Date(Today()-Day(Today())+1),-12),1,0) as Rolling12MonthsFlag,

if(InYearToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar YTD TY],  // All Dates to Date this Year

if(InYearToDate (TempDate, weekstart(today())-1, -1),1,0) as [Calendar YTD PY],  // All Dates to Date Last Year

if(InWeekToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar WTD TY], // All Dates to Date this Week this Year

if(InWeekToDate (TempDate, weekstart(today()-7)-1, 0),1,0) as [Calendar Last WTD TY], // All Dates to Date Last Week this Year

if(InMonthToDate(TempDate, (today())-1, 0),1,0) as [Calendar MTD TY], // All Dates to Date this Year

if(InYear (TempDate, weekstart(today())-1, -1),1,0) as [Calendar FY PY], // All Dates Last Year

if(InYear (TempDate, weekstart(today())-1, 0),1,0) as [Calendar FY TY] // All Dates This Year

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

Num('$(vCalendar_Start_Date)') + IterNo() as mindate,

Num(MonthEnd(Today())) as maxdate

AutoGenerate 1

While IterNo() <= $(vCalendar_Number_Days_Between_Dates);

I have attached the snapshot of the table as is loaded (just the Reporting Date is in numbers)Capture.PNG

Anil_Babu_Samineni

Check this part, I've changed from num to simple date. Now you can check whether it willl or won't

//=== Get min/max dates from Field ===/

LOAD

Date('$(vCalendar_Start_Date)') + IterNo() as mindate,

Date(MonthEnd(Today())) as maxdate

AutoGenerate 1

While IterNo() <= $(vCalendar_Number_Days_Between_Dates);

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)