Date

[shamitshah]

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

[vishsaggi]

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

];

[sunny_talwar · Accepted solution]

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

];

[sunny_talwar]

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

[shamitshah]

Thanks Sunny!