Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
andreina
Contributor
Contributor

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
andreina
Contributor
Contributor
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.
shraddha_g
Partner
Partner

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.
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

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
Partner

date(MonthEnd(TempDate)) As ReportingDate

andreina
Contributor
Contributor
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.

andreina
Contributor
Contributor
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);

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)