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

Transforming a data into a weeknumber

Dear all,

I have a column called "OFF_SWITCH_DAT" with date that looks like this: DD-MM-YYYY HH:MM:SS (like this: 4-1-2016 17:48:50). What I would like to do now is transform this value into a weeknumber.

I have tried this:

WeekName((OFF_SWITCH_DAT),'-') as WeekNum,

But this gives me the following output:

-

Also

WeekName(OFF_SWITCH_DAT) as WeekNum,

Does not work (giving me 2016/04)

Any thougts on how I can get a weeknumber.



Cheers,

Marc

5 Replies
Anonymous
Not applicable
Author

use this Expression and define your datefield instead of hardcoded date

=weekname(timestamp#('4-1-2016 17:48:50','DD-MM-YYYY hh:mm:ss'))

santiago_respane
Specialist
Specialist

Hi,

try something like this:

=Num(Week(YourDateField))

Or this:

=weekname(YourDateField)

Let me know if this helps.

Kind regards,

jonathandienst
Partner - Champion III
Partner - Champion III

First make sure that the OFF_SWITCH_DAT is being correctly loaded as a numeric date value. The easiest way to check is to create a list box for OFF_SWITCH_DAT and seeing whether the dates align to the left (string) or right (numeric date), or a mix.

Then you can get a Week number with Week(OFF_SWITCH_DAT), or a week/year name with WeekName(OFF_SWITCH_DAT).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ToniKautto
Employee
Employee

As suggested above the most likely reason is that the timestamp does not have the expected underlying numerical value.

If the incoming format is the same as your expected format in the app, I would suggest that you setup the app format variables to match the expected output. This should minimize your need for additional formatting.

From your example format string, I noticed that you use capital letters for the time. Try altering the format variables to lower case for the time portion. For consistency, make sure all three variables are aligned format wise.

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY ';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss';

rupamjyotidas
Specialist
Specialist

Try This WeekName(Floor(NumOFF_SWITCH_DAT))).


Thanks