Solved: Re: Months not Joining Properly - Qlik Community

Report Inappropriate Content · ‎2015-08-19

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

qlikmsg4u · ‎2015-08-19

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

View solution in original post

sunny_talwar · ‎2015-08-19

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

LOAD MonthName(Date) as MonthYear

vishsaggi · ‎2015-08-19

Hi John,

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

V.

jonathandienst · ‎2015-08-19

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sasiparupudi1 · ‎2015-08-19

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

Report Inappropriate Content · ‎2015-08-19

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

sunny_talwar · ‎2015-08-19

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

HTH

Best,

Sunny

Report Inappropriate Content · ‎2015-08-19

HI

Please Find the Sample Qvw and data Attached

Please Suggest.

sasiparupudi1 · ‎2015-08-19

Margin:

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

[Sales per day],

[Margin per day]

FROM

Count.xlsx

(ooxml, embedded labels, table is Sheet2);

Report Inappropriate Content · ‎2015-08-19

HI Sasidhar

when i use Your above Script

I am getting the following Result