topic Re: Error in Expression when using MasterCalendar in QlikView

Error in Expression when using MasterCalendar

amber2000 — Tue, 05 May 2015 13:44:25 GMT

Hello everyone,

I'm strugling with my MasterCalendar, when I do a reload I'm getting :

Error in Espression ')' Expected

I can't find where the error occurs.

Does anybody know why please?

Re: Error in Expression when using MasterCalendar

jpenuliar — Tue, 05 May 2015 13:50:59 GMT

You have ":" semicolon in the below:

RESIDENT TempCalendar:

it should be

RESIDENT TempCalendar

Re: Error in Expression when using MasterCalendar

jpenuliar — Tue, 05 May 2015 13:52:32 GMT

and the below lines:

InYearToDate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag
InYearToDate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

Try with If() function

Re: Error in Expression when using MasterCalendar

amber2000 — Tue, 05 May 2015 13:54:19 GMT

How do you mean with the If function?

Re: Error in Expression when using MasterCalendar

jpenuliar — Tue, 05 May 2015 13:54:47 GMT

or below:

YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

Re: Error in Expression when using MasterCalendar

MK_QSL — Tue, 05 May 2015 13:57:17 GMT

MasterCalendar:

Load

DATE#(TempDate, 'YYYYMMDD') as DT_Create_Dates,

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, //Ceil = afronden Tempdate/3 van een non decimaal getal naar een geheel getal

Date(MonthStart (TempDate), 'MMM-YYYY') as MonthYear, //For TrendInformation (alle dagen van de maand naar de 1e dag van de maand brengen anders heb je 31 x januari 2007)

Week (TempDate) & '-' & Year(TempDate) as WeekYear, //For TrendInformation

InYearToDate(TempDate, Today(), 0) * -1 AS CurYTDFlag,

InYearToDate(TempDate, Today(), -1) * -1 AS LastYTDFlag

// Herladen van de tabel TempCalendar, als we dit niet doen moeten we voor elke Datefield 'Month', 'Year' , 'Week' etc. de volledige loop gebruiken van MinMaxTemp tabel

RESIDENT TempCalendar

Order by TempDate ASC;

Re: Error in Expression when using MasterCalendar

amber2000 — Tue, 05 May 2015 13:57:49 GMT

Thanks Jonathan, your last remark did the trick

Re: Error in Expression when using MasterCalendar

amber2000 — Tue, 05 May 2015 16:05:32 GMT

Jonathan,

After testing I see that my Month is not linked to SD_Close_Date.

If I have no selection I get my data, if I make the selection for month 'mrt' then there is no data displayed.

Somewhere there is still something wrong, can you see where?

Kind regards,

Ambertje

Re: Error in Expression when using MasterCalendar

jpenuliar — Tue, 05 May 2015 16:10:36 GMT

Hi Amber,

SD_Close-Date is not included in your Fact table, see below:

Re: Error in Expression when using MasterCalendar

jpenuliar — Tue, 05 May 2015 16:13:11 GMT

The idea of the Master Calendar is to link it to your Date field.

In your case, you have the Master Calendar linked to DT_Create_Dates.

You will have to make another Calendar Table to link to SD_Close_Date.

another way is to look into Common Date technique or Canonical Calendar, quick search in the community will do

Re: Error in Expression when using MasterCalendar

amber2000 — Tue, 05 May 2015 16:14:57 GMT

Sorry I've given the wrong Field name, it should be DT_Create_Dates (SD_Close_Date is from another table),

and as you show in table view this field is linked. Still it isn't showing the dates if I select month

Re: Error in Expression when using MasterCalendar

jpenuliar — Wed, 06 May 2015 08:05:48 GMT

try to remove month filter/listbox and add again,

I am not sure what is happening here, but It worked that way on my end

Re: Error in Expression when using MasterCalendar

amber2000 — Wed, 06 May 2015 12:40:57 GMT

Dear Jonathan,

This is not working for me, but there’s something strange on the table view: The subset ratio is 50% on both tables for field DT_Create_Dates.

So I looked at the script and removed the date format from //DATE#(TempDate, 'YYYYMMDD') as DT_Create_Dates,

to TempDate AS DT_Create_Dates and after the reload the problem was fixed.

Thank you so much for helping me.

Kind regards,

Ambertje