Week Nr is not working

[Anonymous]

Hello,

I'm trying to get the WeekNr from a timestamp, but unfortunately it's not working.

I've tried several formulas:

Week( Date (mydate, 'DD.MM.YYYY')) as xy

Week( Date#...

Week( Timestamp# (...

At the beginning of the scrip I have following:

Set DateFormat = 'DD.MM.YYYY';

I've also checked the OS - Date, Time and Numberformats which are 'DD.MM.YYYY'

The interresting thing is that Date(mydate) returns also '-'

Maybe because 'mydate' is sometimes shown as a number and sometimes as 'dd.mm.yyyy hh:mm:ss'

Any ideas??

Cheers

//chesterluck

[Anonymous]

No Henric is right the problem is the Schichtdatum/mydate and the  

NEST FUNCTIONS

is somehow not working ...

[Michiel_QV_Fan]

Double checked for any typ errors?

I might be a very small issue

[Anonymous]

check the discussion:

In Excel the dates / timestamps are shown just fine (after format)..

Ive uploaded a data set.

Really no idea why thats not working..

cheers

[swuehl]

Your Schichtdatum numbers are not numbers, but read in as text (probably due to the different decimal separator), try:

LOAD

...

Timestamp(alt(num#(Schichtdatum,'#','.',','),Timestamp#(Schichtdatum, 'DD.MM.YYYY hh:mm:ss'))) as SchichtdatumNeu,

...

[hic · Accepted solution]

You have several problems here. The first thing I see when I load your qvd, is that the Schichtdatum is left-aligned, i.e. the numbers are not interpreted as numbers (green). They should be right-aligned. Also, you have a mixture of numbers and dates (red).

Also, when I open the qvd in notepad, I see that the field Schichtdatum has not been correctly interpreted/formatted before it was stored in the qvd.

I would try to create a correct qvd, by using the following formula before the data is stored in the qvd:

Timestamp(Alt(Num#(Schichtdatum,'#','.',','), Timestamp#(Schichtdatum,'DD.MM.YYYY hh:mm:ss')))

HIC

[Anonymous]

and its working

many thx