topic Re: Date format in number in QlikView

Date format in number

Anonymous — Fri, 16 Dec 2016 12:01:56 GMT

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!

Re: Date format in number

Anonymous — Fri, 16 Dec 2016 12:04:59 GMT

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!

Re: Date format in number

PrashantSangle — Fri, 16 Dec 2016 12:06:07 GMT

and your question is????

Regards

Re: Date format in number

shraddha_g — Fri, 16 Dec 2016 12:06:14 GMT

date(TempDate) As CalendarDate,

Re: Date format in number

PrashantSangle — Fri, 16 Dec 2016 12:07:43 GMT

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

Re: Date format in number

Anil_Babu_Samineni — Fri, 16 Dec 2016 12:08:37 GMT

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

Or else

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

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

Re: Date format in number

shraddha_g — Fri, 16 Dec 2016 12:09:09 GMT

date(MonthEnd(TempDate)) As ReportingDate

Re: Date format in number

Anonymous — Fri, 16 Dec 2016 12:10:57 GMT

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.

Re: Date format in number

Anonymous — Fri, 16 Dec 2016 12:55:03 GMT

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)

Re: Date format in number

Anil_Babu_Samineni — Fri, 16 Dec 2016 12:59:11 GMT

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

Re: Date format in number

MarcoWedel — Fri, 16 Dec 2016 23:22:12 GMT

Hi,

I guess you might have changed the document standard format of the ReportingDate field.

Check in Settings/Document Properties/Number tab.

hope this helps

regards

Marco