Trouble with date format

[#1]

I have the following load on one dashboard

SALES:Directory;QUALIFY *;UNQUALIFY ControlDate,;

LOAD Status,

.....

[Decision Date] as [Global Award Date],

[Phase Since],

....

  if(Status = 'Open', [Decision Date],
         if(Status = 'Won', [Decision Date], 
            if(Status = 'Lost', [Decision Date],date([Phase Since])))) as ControlDate FROM [$(QVDPath)Sales.xlsx] (ooxml, embedded labels, table is SAPBW_DOWNLOAD);

I have a second load on a different dashboard:

Revenue:Directory;QUALIFY *;UNQUALIFY ControlDate;

LOAD [Opportunity ID], 

.....

[Decision Date],

[Phase Since],

....

if(Status = 'Open', date([Decision Date]),
         if(Status = 'Won', date([Decision Date]), 
            if(Status = 'Lost', date([Decision Date]),date([Phase Since])))) as ControlDate
         FROM

(ooxml, embedded labels, table is SAPBW_DOWNLOAD);

I have a Calendar:

Range:LOAD
     num(date('01/04/2008')) as startdate, //######## CHANGE HERE ########     max(ControlDate) as enddate //######## CHANGE HERE ########resident SALES;  //######## CHANGE HERE ########

//Peek out the values for later uselet vStart = peek('Range.startdate',-1,'Range')-1;let vEnd = peek('Range.enddate',-1,'Range');let vRange = $(vEnd) - $(vStart);//Remove Range table as no longer neededDrop table Range;
//Generate a table with a row per date between the range aboveDate:UNQUALIFY *;Load
     $(vStart)+recno() as Dateautogenerate $(vRange);
//Calculate the Parts you need to examineDateParts:load
     Date as [ControlDate], //######## CHANGE HERE ########     date(Date,'dd/mm/yyyy') as FullDate,

.......

resident Date;

I used the Calendar on both dashboards ( but I change the  resident from SALES to Revenue)

The first version works but the second does not.

With the second version the control date comes out like 01/01/2011 rather than in the first dashboard which is like 39541 (which I assume is a Julian date)

The input fields Decision date and Phase Since are exactly the same on both of the source spreadsheets (Format General).  I tried date([Decision  Date]) on the second dashboard but to no effect.

Does anyone have a suggestion where the differenvce might lie?

Regards

Jason

[Miguel_Angel_Baeyens]

Hi Jason,

It's indeed a Julian date, since Max() and Min() return only numeric values (as dates are). Try changing this line

$(vStart)+recno() as Date

To this one

Date($(vStart) + RecNo()) as Date

In your calendar table, so the result is a formatted date like "17/01/2012" (or whatever format according to your local OS settings).

Hope that helps.

[#3]

Hi Miguel

Thanks for your  help. I have changed the Calendar to the following:

UNQUALIFY *;

Load

//   $(vStart)+recno() as Date

    Date($(vStart) + RecNo()) as Date

autogenerate $(vRange);

Unfortunatley the ControlDate is still displayed like - 02/01/2003. Any other thoughts?

Regards

Jason

[swuehl]

I would check three things:

1) Are your dates correctly read in as date types (with numerical representation)? If not, check the source format and use date#() or timestamp#() function to read your dates in

2) Check that your format your dates consistently using date() function. It seems that you used date() function on second, but not on first excel table load to format your decision date

3) Check your date format codes: In the last date function, you used

date(Date,'dd/mm/yyyy') as FullDate,

IMHO, 'dd/mm/yyyy' should be replaced by 'DD/MM/YYYY' , at least 'mm' is indicating minutes, not Months, so you will get zero values in the middle for all records, if you are handling Dates (not timestamps).

Hope this helps,

Stefan

edit:

If you don't want your dates to be shown as 17/01/2012, but with a numerical instead, use num() function to display your ControlDates as numericals.

[#5]

Stefan,

Thanks the DD/MM/YYYY corrects the error I was have with the Full date.

The ControlDate is still coming out with 01/02/2003 whether I use Date#() on the intial load of the value or in the IF statement.

The date in the spreadsheet has a format of General , so it is not numeric. What is confusing me is that the data in the past was Generl and the Calendar derived a Julian date!

Regards

Jason

[vijay_iitkgp · Accepted solution]

Hi Jason,

My solution is little strange but it will work for sure.

You can use  Num(makedate(Right(Date,4),mid(Date,4,2),left(Date,2)) as Date

Hope this will help

[#7]

Vijay,

I have tried your fix,but I am still getting 01/01/2012 rather than a Julian date.

Thanks for your response

Jason

[swuehl]

Even if you apply num() function to your current Date field (in an object expression or in the script)?

Please check that your date field  is not just a text / string. I don't think it is, because I think your standard date format should have taken care of it (you are using 'DD/MM/YYYY' as DateFormat, right?), but if you have a mix of date formats, this is sometime the root cause - some of your imported fields may only display a text, that looks like a date, but it is not recognized as date type with also a numerical representation.

But first try with num() function.

Hope this helps,

Stefan

[#9]

Stefan,

The dates I am bringing in from the Excel spreadsheet have a format of general (so I assume that menas they are strings) are in MM/DD/YYYY form.

I have tried Vijay's num(makedate(....)) in the load script as follows:

if(Status = 'Open', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))),
  if(Status = 'Won', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))), 
    if(Status = 'Lost', date(Num((makedate(Right([Decision Date],4),left([Decision Date],2),mid([Decision Date],4,2))))),
    date(Num((makedate(Right([Phase Since],4),left([Phase Since],2),mid([Phase Since],4,2)))))))) as ControlDate

The control date is then used in the Calendar as above

• -          except with DD/MM/YYYY for Full Date

I  have tried both :

$(vStart)+recno() as Date

And  Date($(vStart) + RecNo()) as Date

for  the autogenerate of $(vRange) in the calendar, but  both result in the Control Date being in the form  DD/MM/YYYY and not a Julian date.

Thanks once again for your assistance.

Jason

[vijay_iitkgp]

Hi Jason,

Please do not use date before num. It is again converting num to date