Months not Joining Properly

[#1]

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

[Anonymous]

hi john,

create monthyear field in target table

Regards

Neetha

[sunny_talwar]

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

[sunny_talwar]

I am working with Personal Edition of QlikView so cannot open you qvw, can you share your script?

[sasiparupudi1]

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

[sasiparupudi1]

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

[qlikmsg4u · Accepted solution]

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