Calendar Issue: Year 2013 not showing correctly in chart

[#1]

Hi All,

I have a question on master calendar, hope some one can help to enlighten on this aspect.

Below are the information for your understanding first:

Code:

LET vMinDate = Num(Peek('GS_Case_Log_Start_Date', 0, 'GS_Case_Log'));  //min record value Start date

LET vMaxDate = Num(Peek('GS_Case_Log_Start_Date', -1, 'GS_Case_Log'));  //max record value End Date

LET vToday = num (Today());

//*************** Temporary Calendar ***************

DateField:

Load

  $(vMinDate) + RowNo() -1 as Num,

  date($(vMinDate) + RowNo() -1) as TempDate

AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

//*************** Master GS Case Log Start Date Calendar ***************

MasterCalendar:

Load

TempDate as GS_Case_Log_Start_Date,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

WeekDay(TempDate) as WeekDay,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,

Week(TempDate) & ' ' & Year(TempDate) as WeekYear,

InYearToDate(TempDate, $(vToday),0) * -1 as CurYearFlag,

InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag //In Qlikview, true is -1, false is 0.

Resident DateField

Order By TempDate ASC;

DROP Table DateField;

End Result:

Data:

The chart is displaying data for end date (End year).  I notice that it will display "-" whenever the end date is after June 2014.  However, I not sure where the issue lies at in order to resolve this.  My chart is doing a Sum(Case_Log) with 2 dimensions (subject name & End Year) and Full accumulation.

Please kindly help to enlighten where have I gone wrong.

Thanks

[tresesco]

This could be because of your End Year dimension. Is it a calculated dimension? If so, how do you calculate that?

[#3]

Hi,

Bold words is my calculation for end year.

Code:

//*************** Master GS Case Log End Date Calendar ***************

LET vMinDate2 = Num(Peek('GS_Case_Log_End_Date', 0, 'GS_Case_Log'));  //min record value Start date

LET vMaxDate2 = Num(Peek('GS_Case_Log_End_Date', -1, 'GS_Case_Log'));  //max record value End Date

LET vToday2= num (Today());

//*************** Temporary Calendar ***************

DateField2:

Load

  $(vMinDate2) + RowNo() -1 as Num2,

  date($(vMinDate2) + RowNo() -1) as TempDate2

  AUTOGENERATE $(vMaxDate2) - $(vMinDate2) + 1;

//*************** Master GS Case Log Start Date Calendar ***************

MasterCalendar2:

Load

TempDate2 as GS_Case_Log_End_Date,

Week(TempDate2) as End_Week,

Year(TempDate2) as End_Year,

Month(TempDate2) as End_Month,

Day(TempDate2) as End_Day,

WeekDay(TempDate2) as End_WeekDay,

'Q' & Ceil(Month(TempDate2)/3) as End_Quarter,

Date(MonthStart(TempDate2), 'MMM-YYYY') as End_MonthYear,

Week(TempDate2) & ' ' & Year(TempDate2) as End_WeekYear,

InYearToDate(TempDate2, $(vToday2),0) * -1 as End_CurYearFlag,

InYearToDate(TempDate2, $(vToday2), -1) * -1 as End_LastYTDFlag2 //In Qlikview, true is -1, false is 0.

Resident DateField2

Order By TempDate2 ASC;

DROP Table DateField2;

[tresesco]

If you calculate that in script as you have shown, there should not be an issue with the year calculation. I guess the problem may be with the data or the expression. Please share a sample app.

[#5]

Hi,

As per request.

Thanks

[tresesco]

Not the log, please share sample qvw file.

[#7]

Sry, Should be correct this time.

[rajeshvaswani77]

Hi,

The Year is Null for that subject as per your schema.

Check your linking of GS_STD_Details to GS_BI_Monthly

thanks,

Rajesh Vaswani

[tresesco · Accepted solution]

The variable the part are not getting assigned with right values:

//LET  vMinDate2 = Num(Peek('GS_Case_Log_End_Date', 0, 'GS_Case_Log'));  //min record value Start dat
//LET vMaxDate2 = Num(Peek('GS_Case_Log_End_Date', -1, 'GS_Case_Log'));  //max record value End Date

PLace a Max() and Min() for them prior to this(above) section of code like:

MaxTable:

Load Max(GS_Case_Log_End_Date) as MaxDate,

          Min(GS_Case_Log_End_Date) as MinDate

From <>;

Let   vMaxDate2=Peek('MaxDate');

Let   vMinDate2=Peek('MinDate');

Drop Table MaxTable;

..........

Edit: Single quotes were missed.

[#10]

Will check it out now on both of the issues found.