topic Re: Months not Joining Properly in QlikView

Months not Joining Properly

Wed, 19 Aug 2015 08:33:20 GMT

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

Re: Months not Joining Properly

sunny_talwar — Wed, 19 Aug 2015 08:35:59 GMT

May be force them to be the same even though they look the same:

LOAD MonthName(Date) as MonthYear

Re: Months not Joining Properly

vishsaggi — Wed, 19 Aug 2015 08:36:56 GMT

Hi John,

Will that be ok to upload your sample app. Presuming it could be a formatting issue from your target table.

Re: Months not Joining Properly

jonathandienst — Wed, 19 Aug 2015 08:40:06 GMT

As always, more detail and a sample would help. But taking a wild guess - at least one of the two contains a time value, or you have formatted the m as months (eg MM-YYYY), but they are different dates. In other words they have the same text representation but different underlying values.

Re: Months not Joining Properly

sasiparupudi1 — Wed, 19 Aug 2015 09:02:20 GMT

are the field name same? perhaps you do not have a link between the fields?

Re: Months not Joining Properly

Wed, 19 Aug 2015 09:10:00 GMT

Hi Sunny,

Thanks.

now the months got joined but other fields in the target table are not properly mapped to the corresponding months.

I did populated the MONTH from mastercalendar and the targets in table box. screenshot below

Please help me on this

Re: Months not Joining Properly

sunny_talwar — Wed, 19 Aug 2015 09:11:57 GMT

Can you share the script to get a better understanding of what is going on.

HTH

Best,

Sunny

Re: Months not Joining Properly

Wed, 19 Aug 2015 09:26:39 GMT

Please Find the Sample Qvw and data Attached

Please Suggest.

Re: Months not Joining Properly

sasiparupudi1 — Wed, 19 Aug 2015 09:43:30 GMT

Margin:

LOAD Date#(Month,'MMM') as DEC_Month,

[Sales per day],

[Margin per day]

FROM

Count.xlsx

(ooxml, embedded labels, table is Sheet2);

Re: Months not Joining Properly

Wed, 19 Aug 2015 09:48:15 GMT

HI Sasidhar

when i use Your above Script

I am getting the following Result

Re: Months not Joining Properly

Anonymous — Wed, 19 Aug 2015 09:50:39 GMT

hi john,

create monthyear field in target table

Regards

Neetha

Re: Months not Joining Properly

sunny_talwar — Wed, 19 Aug 2015 09:51:29 GMT

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

Re: Months not Joining Properly

sunny_talwar — Wed, 19 Aug 2015 09:52:19 GMT

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

Re: Months not Joining Properly

sasiparupudi1 — Wed, 19 Aug 2015 10:01:35 GMT

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

Re: Months not Joining Properly

sasiparupudi1 — Wed, 19 Aug 2015 10:03:26 GMT

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

Re: Months not Joining Properly

qlikmsg4u — Wed, 19 Aug 2015 10:06:48 GMT

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