Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) ;
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..
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..