Logic help related to Current Week Vs last Year Week.

[lalitkgehlot89]

Hi,

In Sample data I have three Fields, Storeid, Date & Sales Amount.

I want to make a Straight table which show sales data of current week and last year same week sales data.

And first week should be from 1Jan and after that secound,third,fourth... should be start from Monday.

for more reference data given in weekcal file.

Please help me out to create the logic for the same.

Thanks,

Lalit Kumar

[avkeep01]

Hi Lalit,

you could use the following expression in the script:

LOAD STOREID,

     [Posting Date],

    MONTH([Posting Date]) AS Month,

RIGHT('00'&IF(DAYNUMBEROFYEAR([Posting Date])<=7 AND WEEK([Posting Date])>2,1,IF(DAYNUMBEROFYEAR([Posting Date])>=358 AND WEEK([Posting Date])<2,53,WEEK([Posting Date]))),2) AS Week,

IF(YEAR([Posting Date]) = 2017, 1) AS _CurrentYear,

IF(YEAR([Posting Date]) = 2017-1, 1) AS _LastYear,

     Amount

FROM

[Sample data.xlsx]

(ooxml, embedded labels, table is Document_CH224);

One thing: I just hardcoded 2017, you probably want to make it dynamically.

[lalitkgehlot89]

Hi A.M.,

As per your logic In year 2017 1st week has 8 days. but according my requirement 1week should have only day. and second week should be start  from Monday.

[avkeep01]

Hi Lalit,

I cheated a bit on the weeks because 1-1-2017 will be week 52 (or 53). So I updated the script.

- 12:00 update - needed to tweak the years not starting at 1-1, so I created a first date flag.

sourcetable:

LOAD STOREID,

     [Posting Date],

     MONTH([Posting Date]) AS Month,

     Amount

FROM

[Sample data.xlsx]

(ooxml, embedded labels, table is Document_CH224);

LEFT JOIN (sourcetable) LOAD

MIN([Posting Date]) AS [Posting Date],

1 AS Yearstart

RESIDENT sourcetable

GROUP BY YEAR([Posting Date]);

table:

LOAD

STOREID,

[Posting Date],

Month,

IF(YEAR([Posting Date]) = 2017, 1) AS _CurrentYear,

IF(YEAR([Posting Date]) = 2017-1, 1) AS _LastYear,

IF(Yearstart = 1,IF([Posting Date] = YEARSTART([Posting Date]) , 1,WEEK([Posting Date])), IF(NUM(WEEKDAY([Posting Date]))=0 AND [Posting Date] <> PEEK('Posting Date',-1),PEEK('Week',-1)+1,PEEK('Week',-1))) AS Week,

WEEKDAY([Posting Date]) AS Weekday,

Amount

RESIDENT sourcetable

ORDER BY [Posting Date];

DROP TABLE sourcetable;

[lalitkgehlot89]

Thanks a lot A.M.

Please let me how to right this expression in straight table for current year week 48 sales data and Last year week 48 sales data comparison.

[avkeep01]

Hi Lalit,

I've made a chart with [Week] as dimension.

In the expression I created:

SUM({$<_CurrentYear={1}>} Amount) and SUM({$<_LastYear={1}>} Amount).

I've attached an example.

[avkeep01]

Just to let you know: the weeks that i've created aren't the same as the weeks on the calendar. I just started counting from the first day of the year and every monday the week gets 1 higher. So calender week 48 in the current year is basically the 48th monday. that can be different for the current year and the last year. Keep that in mind.

[lalitkgehlot89]

Hi A.M.,

I want only three fields in chart 1) StoreiD, 2)Week 48 current Amounnt 3) week 48 Last Year.

[lalitkgehlot89]

Hi A.M.

Yes you are right, but my requirement is something like this comparison should be between current year week(48) vs last year week(48). doesn't matter days and dates are same or not.

[avkeep01]

Dimensions:

- StoreID

- Week

Expressions

- SUM({$<_CurrentYear={1},Week={48}>} Amount)

- SUM({$<_LastYear={1},Week={48}>} Amount).