topic Re: YearWeek Dates in QlikView

YearWeek Dates

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

Hi,

Actually we have compared the weekly sales for 2016 and 2015 like 206 & 205 same week sales.

Our logic is working fine before February-16 but after that it's not showing correct.

I have checked in DB it's fine.Below were the logic used in our qlikview script.

Issue:

Let vDateYesterday = (year(today())-1900)&num(month(today()),'00')&num(day(today()),'00');

Let vDateYesterdayLY = (year(today())-1901)&num(month(today()),'00')&num(day(today()),'00');

Let vBOM = (year((today()-7))-1900)&num(month(today()-7),'00')&num(day(today()-7),'00');

Let vBOMLY = (year(today()-6)-1901)&num(month(today()-6),'00')&num(day(today()-6),'00');

Let vWeekLY=weekyear(today())-1&num(week(AddYears(today()-1,-1)),'00');

Let vWeekTY=weekyear(today()-1)&num(week(today()-1),'00');

Issue: If we select YearWeek field as 201512(year=2015,week=12) it should select date from 16\03\2015 to 22\03\2015 but it showing 16\03\2015 to 21\03\2015.

MainTable:

Load "Company",

"Item_Completed_Date" as DATE,

FISCALYEAR,

FYStart,

QtrEnd,

QTRENDDT,

QtrSt,

QTRSTARTDT,

QUARTER;

SQL select * FROM Datamart.dbo."RRR_TIU_TDTDF" a, TUUYUII b

where a.Item_Completed_Date <= b.QTRENDDT AND a.Item_Completed_Date >= b.QTRSTARTDT

and (Item_Completed_Date BETWEEN $(vBOMLY) AND $(vDateYesterdayLY) OR Item_Completed_Date BETWEEN $(vBOM) AND $(vDateYesterday));

Inner join (MainTable)

DateRefs:

LOAD Date400 as DATE,

YW as YearWeek;

SQL Select *

FROM Datamart.dbo.dateyf

where YW = $(vWeekLY) or YW = $(vWeekTY) ;

Re: YearWeek Dates

nareshthavidishetty — Tue, 22 Mar 2016 11:39:00 GMT

Hi,

Actually we have compared the weekly sales for 2016 and 2015 like 206 & 205 same week sales.

Our logic is working fine before February-16 but after that it's not showing correct.

I have checked in DB it's fine.Below were the logic used in our qlikview script.

Issue: If we select YearWeek field as 201512(year=2015,week=12) it should select date from 16\03\2015 to 22\03\2015 but it showing 16\03\2015 to 21\03\2015.

SCRIPT:

Let vDateYesterday = (year(today())-1900)&num(month(today()),'00')&num(day(today()),'00');

Let vDateYesterdayLY = (year(today())-1901)&num(month(today()),'00')&num(day(today()),'00');

Let vBOM = (year((today()-7))-1900)&num(month(today()-7),'00')&num(day(today()-7),'00');

Let vBOMLY = (year(today()-6)-1901)&num(month(today()-6),'00')&num(day(today()-6),'00');

Let vWeekLY=weekyear(today())-1&num(week(AddYears(today()-1,-1)),'00');

Let vWeekTY=weekyear(today()-1)&num(week(today()-1),'00');

MainTable:

Load "Company",

"Item_Completed_Date" as DATE,

FISCALYEAR,

FYStart,

QtrEnd,

QTRENDDT,

QtrSt,

QTRSTARTDT,

QUARTER;

SQL select * FROM Datamart.dbo."RRR_TIU_TDTDF" a, TUUYUII b

where a.Item_Completed_Date <= b.QTRENDDT AND a.Item_Completed_Date >= b.QTRSTARTDT

and (Item_Completed_Date BETWEEN $(vBOMLY) AND $(vDateYesterdayLY) OR Item_Completed_Date BETWEEN $(vBOM) AND $(vDateYesterday));

Inner join (MainTable)

DateRefs:

LOAD Date400 as DATE,

YW as YearWeek;

SQL Select *

FROM Datamart.dbo.dateyf

where YW = $(vWeekLY) or YW = $(vWeekTY) ;

Thanks..

Re: YearWeek Dates

nareshthavidishetty — Tue, 22 Mar 2016 11:44:13 GMT

Hi,

Where,

Let vDateYesterday = (year(today())-1900)&num(month(today()),'00')&num(day(today()),'00'); = 1160315

Let vDateYesterdayLY = (year(today())-1901)&num(month(today()),'00')&num(day(today()),'00'); = 1150316

Let vBOM = (year((today()-7))-1900)&num(month(today()-7),'00')&num(day(today()-7),'00'); = 1150322

Let vBOMLY = (year(today()-6)-1901)&num(month(today()-6),'00')&num(day(today()-6),'00');= 1160322

Let vWeekLY=weekyear(today())-1&num(week(AddYears(today()-1,-1)),'00'); = 201512

Let vWeekTY=weekyear(today()-1)&num(week(today()-1),'00'); = 201612

Thanks..