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));
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));
