topic Date in QlikView

Date

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to compare the Units sold week by week by using the following in set analysis {<[Wk Start] = {$(=Max([Wk Start]))} and {<[Wk Start] = {$(=Max([Wk Start])-1)}.

Currently the date format for Wk Start is YYMMDD (e.g.20160627). This is causing a problem in using the above set analysis.

Any idea how I can amend the script to he reflect the date correctly ( in the format DD-MM-YYYY)? Any other ideas?

Thanks

Shamit

Re: Date

vishsaggi — Fri, 29 Jul 2016 04:40:07 GMT

May be try this:

LOAD*,

Date(MakeDate(Left([Wk Start],4),Mid([Wk Start],5,2),Right([Wk Start],2)),'DD-MM-YYYY') AS WeekStart;

LOAD * INLINE [

Fin Yr, Month, Wk Start, FY Wk, Cal Wk, Item, Units Sold

2016, 201606, 20160627, 53, 27, A, 1

2016, 201606, 20160627, 53, 27, B, 2

2016, 201606, 20160627, 53, 27, C, 3

2016, 201606, 20160627, 53, 27, D, 4

2017, 201607, 20160704, 1, 28, A, 2

2017, 201607, 20160704, 1, 28, B, 6

2017, 201607, 20160704, 1, 28, C, 8

2017, 201607, 20160704, 1, 28, D, 10

];

Re: Date

sunny_talwar — Fri, 29 Jul 2016 04:42:44 GMT

You can change the date format like this:

LOAD *,

Date(Date#([Wk Start], 'YYYYMMDD')) as Date;

LOAD * INLINE [

Fin Yr, Month, Wk Start, FY Wk, Cal Wk, Item, Units Sold

2016, 201606, 20160627, 53, 27, A, 1

2016, 201606, 20160627, 53, 27, B, 2

2016, 201606, 20160627, 53, 27, C, 3

2016, 201606, 20160627, 53, 27, D, 4

2017, 201607, 20160704, 1, 28, A, 2

2017, 201607, 20160704, 1, 28, B, 6

2017, 201607, 20160704, 1, 28, C, 8

2017, 201607, 20160704, 1, 28, D, 10

];

Re: Date

sunny_talwar — Fri, 29 Jul 2016 04:45:17 GMT

And I guess the set analysis will need to be like this:

{<[Wk Start] = {$(=Date(Max([Wk Start])))}

{<[Wk Start] = {$(=Date(Max([Wk Start])-7))}.or {<[Wk Start] = {$(=Date(Max([Wk Start], 2)))}.

Re: Date

shamitshah — Fri, 29 Jul 2016 06:45:06 GMT

Thanks Sunny!