Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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:MMSmiley FrustratedS (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
hrlinder
Honored Contributor

Re: Transforming a data into a weeknumber

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_respan
Valued Contributor

Re: Transforming a data into a weeknumber

Hi,

try something like this:

=Num(Week(YourDateField))

Or this:

=weekname(YourDateField)

Let me know if this helps.

Kind regards,

MVP
MVP

Re: Transforming a data into a weeknumber

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

Employee
Employee

Re: Transforming a data into a weeknumber

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
Valued Contributor

Re: Transforming a data into a weeknumber

Try This WeekName(Floor(NumOFF_SWITCH_DAT))).


Thanks