Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts
I have a master calendar and target table
my targets table having a same field month
In my master calendar starts from January 2015 to till date
but my targets table is having monthly targets for entire 2015
both month fields are in same format but in dash board its showing two different month field
Please Suggest
hi john,
create monthyear field in target table
Regards
Neetha
Seems like the table which include Target1 and Target2 information, you need to do this:
LOAD Date#(Month, 'MMM') as Month
Jan, Feb, Mar where Target1 and Target 2 are given are not read as Dual Month field.
HTH
Best,
Sunny
I am working with Personal Edition of QlikView so cannot open you qvw, can you share your script?
try
Let varMinDate = NUM(MAKEDATE($(vReviewYear),1,1));
Let varMaxDate = NUM(FLOOR($(vToday)-1));
//Let varMaxDate = NUM($(vToday));
//Let varMaxDate = NUM(MonthEnd($(vToday)));
Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
//Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
MasterCalendar:
Load *, DEC_WeekYear & '-' & NUM(DEC_WeekNumber,'00') as DEC_YearWeek;
Load *,
Div( DECDDT - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,
Year( DEC_WeekYearRefDate ) as DEC_WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), DECDDT, DEC_WeekRefDate )) + $(vCal_RD) - 1) as DEC_WeekYearRefDate ;
Load *,
Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as DEC_WeekRefDate ;
Load
TempDate AS DECDDT,
TempDate AS DEC_Date,
NUM(TempDate) AS DEC_NumDate,
WeekStart(TempDate, 0, $(vCal_FD) ) as DEC_WeekStart ,
Year(TempDate) As DEC_Year,
num(Month(TempDate)) As DEC_Month,
//MonthName(TempDate) As DEC_MonthYear,
IF(TempDate<=Today(),1,0) as DEC_TD,
Day(TempDate) As DEC_Day,
YeartoDate(TempDate)*-1 as DEC_CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as DEC_LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,
WeekDay(TempDate) as DEC_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
join(MasterCalendar)
Margin:
LOAD num(Month(Date#(trim(Month),'MMM'))) as DEC_Month,
[Sales per day],
[Margin per day]
FROM
Count.xlsx
(ooxml, embedded labels, table is Sheet2) ;
or
Let varMinDate = NUM(MAKEDATE($(vReviewYear),1,1));
Let varMaxDate = NUM(FLOOR($(vToday)-1));
//Let varMaxDate = NUM($(vToday));
//Let varMaxDate = NUM(MonthEnd($(vToday)));
Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
//Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
MasterCalendar:
Load *, DEC_WeekYear & '-' & NUM(DEC_WeekNumber,'00') as DEC_YearWeek;
Load *,
Div( DECDDT - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,
Year( DEC_WeekYearRefDate ) as DEC_WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), DECDDT, DEC_WeekRefDate )) + $(vCal_RD) - 1) as DEC_WeekYearRefDate ;
Load *,
Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as DEC_WeekRefDate ;
Load
TempDate AS DECDDT,
TempDate AS DEC_Date,
NUM(TempDate) AS DEC_NumDate,
WeekStart(TempDate, 0, $(vCal_FD) ) as DEC_WeekStart ,
Year(TempDate) As DEC_Year,
Month(TempDate) As DEC_Month,
//MonthName(TempDate) As DEC_MonthYear,
IF(TempDate<=Today(),1,0) as DEC_TD,
Day(TempDate) As DEC_Day,
YeartoDate(TempDate)*-1 as DEC_CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as DEC_LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,
WeekDay(TempDate) as DEC_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
join(MasterCalendar)
Margin:
LOAD Month(Date#(trim(Month),'MMM')) as DEC_Month,
[Sales per day],
[Margin per day]
FROM
Count.xlsx
(ooxml, embedded labels, table is Sheet2) ;
Let varMinDate = NUM(MAKEDATE($(vReviewYear),1,1));
Let varMaxDate = NUM(FLOOR($(vToday)-1));
//Let varMaxDate = NUM($(vToday));
//Let varMaxDate = NUM(MonthEnd($(vToday)));
Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
//Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
MasterCalendar:
Load *, DEC_WeekYear & '-' & NUM(DEC_WeekNumber,'00') as DEC_YearWeek;
Load *,
Div( DECDDT - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,
Year( DEC_WeekYearRefDate ) as DEC_WeekYear;
Load *,
Date( YearStart( If( $(vCal_BW), DECDDT, DEC_WeekRefDate )) + $(vCal_RD) - 1) as DEC_WeekYearRefDate ;
Load *,
Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as DEC_WeekRefDate ;
Load
TempDate AS DECDDT,
TempDate AS DEC_Date,
NUM(TempDate) AS DEC_NumDate,
WeekStart(TempDate, 0, $(vCal_FD) ) as DEC_WeekStart ,
Year(TempDate) As DEC_Year,
Month(TempDate) As DEC_Month,
Num(Month(TempDate)) As DEC_Month_Num,
MonthName(TempDate) As DEC_MonthYear,
IF(TempDate<=Today(),1,0) as DEC_TD,
Day(TempDate) As DEC_Day,
YeartoDate(TempDate)*-1 as DEC_CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as DEC_LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,
WeekDay(TempDate) as DEC_WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Margin:
LOAD Month(Date#(trim(Month),'MMM')) as DEC_Month,
[Sales per day],
[Margin per day]
FROM
Count.xlsx
(ooxml, embedded labels, table is Sheet2) ;