topic Re: Error in Calculated dimension in QlikView

Error in Calculated dimension

Tue, 27 Aug 2013 10:32:57 GMT

Hi,

I have Qliview applications in Access point and in all appliactions for Bar and Line chart items I am getting the message as

//Error in Calculated dimension .

This is happening in every Bar and Line chart items.

Could anyone let me know why it is like that please.

Thanks.

Re: Error in Calculated dimension

CELAMBARASAN — Tue, 27 Aug 2013 10:41:40 GMT

Could you post the expression your using for the dimension?

Re: Error in Calculated dimension

Tue, 27 Aug 2013 11:23:12 GMT

Hi,

The Expression is

IF(FiscalWeekSerial >=($(curFiscalWeekSeq)-4) AND FiscalWeekSerial<=$(curFiscalWeekSeq),FiscalWeek)

Re: Error in Calculated dimension

CELAMBARASAN — Tue, 27 Aug 2013 11:24:39 GMT

Expression used in curFiscalWeekSeq?

Re: Error in Calculated dimension

Tue, 27 Aug 2013 11:43:48 GMT

The Dimension expression is

IF(FiscalWeekSerial >=($(curFiscalWeekSeq)-4) AND FiscalWeekSerial<=$(curFiscalWeekSeq),FiscalWeek)

and in Expression tab the expression is

Count(Distinct(CaseID))

Re: Error in Calculated dimension

CELAMBARASAN — Tue, 27 Aug 2013 11:48:30 GMT

Hi,

What about FiscalWeek? and what about curFiscalWeekSeq variable expression?

Re: Error in Calculated dimension

tresB — Tue, 27 Aug 2013 11:50:15 GMT

Check if all the fields and variables (if any) are still valid, might be some of them are not being created as expected after the recent load. Assuming that, it was working fine sometime back.

Re: Error in Calculated dimension

Tue, 27 Aug 2013 11:54:22 GMT

Yes you are right it's worked perfect some time back today only I am getting this problem and I am not able to see one chart item completely. please help me as I am really confusing here

Re: Error in Calculated dimension

Tue, 27 Aug 2013 11:55:44 GMT

Could you please let me a bit more clear as In the Variables Overview tab I am not able to see any values for FiscalWeek and curFiscalWEeekSeq

Re: Error in Calculated dimension

CELAMBARASAN — Tue, 27 Aug 2013 12:00:34 GMT

I guess curFiscalWeekSeq this would be variable because you have included in the dollar expressions.

And FiscalWeek would be field name. Have you check that.

If possible attach sample app. will check it.

Re: Error in Calculated dimension

tresB — Tue, 27 Aug 2013 12:04:52 GMT

So you are calculating these variables in the script. That means, after the last relaod, these variables have not gotten any values assigned. Please check the logic in the script for these variables. Is there any date hard coded in the script?

Re: Error in Calculated dimension

Tue, 27 Aug 2013 12:16:29 GMT

I have this script from Master calendar

MasterCalendar_ACCDB:

LOAD*,

FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;

LOAD*,

YearStart(Date,0,7) AS FiscalYearStart,

YearName(Date,0,7) AS FiscalYear,

AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,

ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,

'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;

LOAD

//DATE(FLOOR(Date),'DD/MM/YYYY') AS Date, //removing floor for testing 12092012-neeraj

Timestamp(Date) as Date_TS,

Date#(Date(Date)) AS Date,

IF(Date = Today(),1) AS Today,

week(Date) AS Week,

Year(Date) AS Year,

Month(Date) AS Month,

Day(Date) AS Day,

WeekDay(Date) AS WeekDay,

ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,

Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,

Week(Date) & '-' & Year(Date) AS WeekYear,

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

InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;

LOAD

Date($(varMinDate) + RecNo() - 1) AS Date

AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);

Re: Error in Calculated dimension

tresB — Tue, 27 Aug 2013 12:21:20 GMT

How are the variables(varMaxDate, varMinDate, FiscalWeek and curFiscalWEeekSeq) defined?

Re: Error in Calculated dimension

Tue, 27 Aug 2013 12:33:32 GMT

This way I created

//--------------------------------------------------------------------------------------
// Generate Master Calendar
//--------------------------------------------------------------------------------------

TRACE Creating Master Calendar;

/* Load transaction TABLE to get earliest and latest dates */

MinMax:

LOAD Max(TIMESTAMP([Addition Date])) AS MaxDate,
Min(TIMESTAMP([Addition Date])) AS MinDate
FROM
[..\QVD_Closures\TblCase.qvd]
(qvd);

// EXIT SCRIPT;
/* STORE earliest and latest dates in variables */

LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax'));

// EXIT SCRIPT;
/* STORE todays date in variable */

LET varToday = Num(Today());

/* Inline TABLE mapping months to quarters */

QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];

/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */

//MasterCalendar_MDB:
MasterCalendar:

//MasterCalendar_forwhen:

LOAD*,
FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;
LOAD*,
YearStart(Date,0,7) AS FiscalYearStart,
YearName(Date,0,7) AS FiscalYear,
AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
//DATE(FLOOR(Date),'DD/MM/YYYY') AS Date, //removing floor for testing 12092012-neeraj
Timestamp(Date) as Date_TS,
Date#(Date(Date)) AS Date,
IF(Date = Today(),1) AS Today,
week(Date) AS Week,
Year(Date) AS Year,
Month(Date) AS Month,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay,
ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
Week(Date) & '-' & Year(Date) AS WeekYear,
InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);

/* DROP Temporary TABLE */

DROP TABLE MinMax;

STORE MasterCalendar INTO D:\Qlikview\MasterCalendar.qvd;

DROP Table MasterCalendar;

//EXIT SCRIPT;

Re: Error in Calculated dimension

rajeshvaswani77 — Tue, 27 Aug 2013 12:37:14 GMT

Why are you dropping and storing the master calender? You can comment the drop statement and leave it in the memory, not sure if you have to store this in the QVD?

thanks,

Rajesh Vaswani

Re: Error in Calculated dimension

tresB — Tue, 27 Aug 2013 12:47:25 GMT

You wrote,

"Could you please let me a bit more clear as In the Variables Overview tab I am not able to see any values for FiscalWeek and curFiscalWEeekSeq".

I wanted to see definition of these variables in the script as well.

Re: Error in Calculated dimension

rajeshvaswani77 — Tue, 27 Aug 2013 12:49:15 GMT

Please check Settings --> Variable Overview. you must be able to see all the variables declared here,

thanks,

Rajesh Vaswani