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]

Hello,

The null values are because of the wrong max value. While you are creating master calendar, the vMaxDate2 variable is picking 06/05/2014 which is wrong. Therefore, your master calendar has no dates behind that date while your transactional data is having dates beyond that. If you use max(), min() functions as i showed in my earlier post, it would get the right max/min values and then the issue should get resolve.

[#3]

Hi Tresesco,

I am following your code:

MaxTable:

Load Max('GS_Case_Log_End_Date') as MaxDate,

          Min('GS_Case_Log_End_Date') as MinDate

Resident GS_Case_Log;

LET  vMaxDate2=Peek('MaxDate');

LET vMinDate2=Peek('MinDate');

LET vToday2= num (Today());

Drop Table MaxTable;

however, it showed this error:

[tresesco]

This is possibly because of the variable expansion. Try using num(), like:

LET  vMaxDate2=Num(Peek('MaxDate'));

LET vMinDate2=Num(Peek('MinDate'));

You can have a trace command to check what is the value getting assigned to the variables.

Trace vMaxDate ; Sleep 1000;   // or Trace $(vMaxDate)  

[#5]

Hi,

I tried your trace command:

I trace both maxdate and mindate.

Maxdate has value and when it goes to mindate, it show the error.

[tresesco]

i guess variables are not being used/assigned properly try :

LET  vMaxDate2=Num(Peek('MaxDate'));

LET vMinDate2=Num(Peek('MinDate'));

Trace 'vMaxDate2  is : ' & vMaxDate2;

Trace 'vMinDate2  is : ' & vMinDate2;

Trace 'vMaxDate2  is dollar : ' & $(vMaxDate2);

Trace 'vMinDate2  is  dollar : ' & $(vMinDate2);

Exit Script;

Then let me know the output.

[#7]

Hi,

[tresesco]

It shows that variables are not getting values. Check the following section of the code placed properly,

LET  vMaxDate2=Num(Peek('MaxDate'));

LET vMinDate2=Num(Peek('MinDate'));

[#9]

Thanks, found the issue.

I put quote at the MaxTable, it was taking string as MaxDate, hence empty.

Thanks for your help, Tresesco!

MaxTable:

Load

Max(GS_Case_Log_End_Date) as MaxDate,  //Should not put quote

Min(GS_Case_Log_End_Date) as MinDate //Should not put quote

Resident GS_Case_Log;

LET vMaxDate2=Num(Peek('MaxDate'));

LET vMinDate2=Num(Peek('MinDate'));

LET vToday2= num (Today());

[tresesco]

ahh ! , I should have noticed that. Anyway, great! it's working finally!

[#11]

thanks again for ur help, appreciate it.