topic Re: Logic help related to Current Week Vs last Year Week. in QlikView

Logic help related to Current Week Vs last Year Week.

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

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

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 10:25:18 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

lalitkgehlot89 — Mon, 27 Nov 2017 10:41:46 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 10:54:09 GMT

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;

Re: Logic help related to Current Week Vs last Year Week.

lalitkgehlot89 — Mon, 27 Nov 2017 11:19:31 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 11:23:16 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 11:35:11 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

lalitkgehlot89 — Mon, 27 Nov 2017 11:36:44 GMT

Hi A.M.,

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

Re: Logic help related to Current Week Vs last Year Week.

lalitkgehlot89 — Mon, 27 Nov 2017 11:50:00 GMT

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.

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 11:50:14 GMT

Dimensions:

- StoreID

- Week

Expressions

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

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

Re: Logic help related to Current Week Vs last Year Week.

lalitkgehlot89 — Mon, 27 Nov 2017 12:05:14 GMT

This one will not work on next week.

Re: Logic help related to Current Week Vs last Year Week.

avkeep01 — Mon, 27 Nov 2017 12:10:31 GMT

Yes, you would make week={48} dynamically.

2 examples:

- SUM({$<_CurrentYear={1},Week={$(=MAX(Week))}>} Amount) // based on selection

- Create variable: vWeek = MAX(Week)

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