topic Re: error when reloading the script in QlikView

error when reloading the script

Fri, 16 Jan 2015 11:44:46 GMT

hi all,

I get the following error: when I Re_load the following script after adding the following line:

(Year([Date_Dt])– 1) * 12 + Num(Month([Date_Dt])) As MonthId

below is the script:

//Adding the Calendar
"Calendar":

LOAD
[Date_Dt],

//Standard Date Objects
Day([Date_Dt]) AS DayOfMonth,
WeekDay([Date_Dt]) AS DayName,
Week([Date_Dt]) AS WeekOfYear,
Month([Date_Dt]) AS Month,
Num(Month([Date_Dt])) AS MonthNum,
'Q' & Ceil(Month([Date_Dt])/3) AS Quarter,
Year([Date_Dt]) AS Year,
(Year([Date_Dt])– 1) * 12 + Num(Month([Date_Dt])) As MonthId

RESIDENT "FACT_POLICY_SNAP_DLY"
;

Re: error when reloading the script

Peter_Cammaert — Fri, 16 Jan 2015 11:51:51 GMT

For me, the image of the error is blank. Can you upload the screenshot again, or copy the text of the message?

Re: error when reloading the script

Fri, 16 Jan 2015 11:55:09 GMT

hi Peter,

below is the screenshot:

Re: error when reloading the script

Fri, 16 Jan 2015 11:56:00 GMT

Still blank

Re: error when reloading the script

vikasmahajan — Fri, 16 Jan 2015 11:58:44 GMT

Qlikview Creates Log file in Text Please check this log file if possible attach so that anybody can guide you on

syntax error

HTH

Vikas

Re: error when reloading the script

rubenmarin — Fri, 16 Jan 2015 11:59:58 GMT

HI Judas, I can't see the screenshot, can you reattach?

Anyway, what are you trying to get in MonthId? ie, for the actual date (16/01/2015) it will return:

(2015-1)*12+1=24169

Usually a number format for year & month is created this way:

Year([Date_Dt])) * 100 + Num(Month([Date_Dt])) As MonthId

This will return 201501 for 16/01/2015

Re: error when reloading the script

Fri, 16 Jan 2015 12:02:30 GMT

Error msg:

Error in expression:')' expected

Re: error when reloading the script

Fri, 16 Jan 2015 12:04:53 GMT

Error in expression:
')' expected
"Calendar":

LOAD
[Date_Dt],

Day([Date_Dt]) AS DayOfMonth,
WeekDay([Date_Dt]) AS DayName,
Week([Date_Dt]) AS WeekOfYear,
Month([Date_Dt]) AS Month,
Num(Month([Date_Dt])) AS MonthNum,
'Q' & Ceil(Month([Date_Dt])/3) AS Quarter,
Year([Date_Dt]) AS Year,
(Year([Date_Dt])– 1) * 12 + Num(Month([Date_Dt])) As MonthId

RESIDENT "FACT_POLICY_SNAP_DLY"

Re: error when reloading the script

rubenmarin — Fri, 16 Jan 2015 12:10:34 GMT

Hi Judas, rewrite the '-', in my qv it's readed as an unrecognized symbol.

Re: error when reloading the script

vikasmahajan — Fri, 16 Jan 2015 12:10:43 GMT

Try this

( Year([Date_Dt])– 1) * 12 + Num(Month([Date_Dt])) ) As MonthId

Re: error when reloading the script

Fri, 16 Jan 2015 12:15:12 GMT

Hi Judas,

Try

(Year([Date_Dt])- 1) * 12 + Num(Month([Date_Dt])) As MonthId

literally copy and paste it. Your minus character doesn't look to be right, so might be the reason

–

hope that helps

Joe

Re: error when reloading the script

Fri, 16 Jan 2015 12:16:35 GMT

hi vikas,

I am trying to create a month id to use when I create the dates comparisons...check the info below:

Point In Time Reporting

I have always believed that Set Analysis’ Raison d’être is to satisfy a basic need in any BI Tool: the ability to perform “Point In Time” Analysis. But, needless to say, it is also amazingly useful for the fulfillment of a bunch of other special needs. In this post, I will cover the specifics of Point In Time Reporting with Set Analysis.

Update 2012/12/13: Barry Harmsen and I have just released our book QlikView 11 for Developers. While it is not free, it does offer a cost-friendly, guided way to learn QlikView and contains an entire chapter about Set Analysis. More information can be found by clicking here.

Step by Step

We have already reviwed Set Analysis Syntax here and the concept of Dynamic Record Sets here. So now, we will combine the acquired knowledge to deliver a real-life application of Set Analysis.

Let’s say the user has the following Selection State: Year = 2010; Quarter = Q4; Month = November. The user is analyzing Sales and needs to compare this period versus the previous Month (October, in this case). How do we accomplish this? This one is simple.

The basic idea is to tell QlikView to assign new value to the field Month. Remember how we did it in the previous post?

Determine the value to be assigned

We can use an aggregation function to determine the value we want to assign to the field Month, for example: Max(Month) – 1. Depending on the way you generated the field Month, it might give you the correct result (10, for October). But if it does not have a numeric representation, meaning it is just a literal value, it will return null.

However, even if it yields a numeric value, you can not assign that value to the field Month because it does not exist as is, we have to assign the exact value, as it exists in our table, to the appropriate field (‘October’ as a literal, to the field Month). So our approach will be to have another field, named [Month (#)] which will contain the numeric representation of the field Month. That way, we can assign the result of the expression Max([Month (#)]) – 1 to the field [Month (#)].

Our preliminar expression

The Set expression to assign the obtained value to the field [Month (#)] would be:

{$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>}. (Check here if you are having trouble understanding the syntax).

The next step would be to use this set expression with our Aggregation function:

Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>} Sales)

But there is a problem! That expression will return zero because the value we are trying to retrieve is actually excluded by the current selection state of the user: The user has specifically selected November, therefore reducing the record set to only the records associated with that value. How do we retrieve a value that is currently excluded?

A few adjustments

The answer is: ignore the user’s selection for that specific field. This is accomplished by assigning a blank value to that field in our set expression:

{$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >}

That will first retrieve the record set for all the available months, unless they are excluded by another field selection, and then assign the value we want (10) to the specified field ([Month (#)]), therefore returning at he end the record set for October only. The order of the field assignments in our Set Expression is not of importance. Our final expression is:

Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >} Sales)

Another example

Easy huh? Great! Now, Let’s say the user’s selection State is: Year = 2010; Quarter = Q4; Month = October. Will the above expression return the sales for September? The answer is No. Can you guess why?

Correct! Because the month we want is not only excluded by the field selection for Month, but also by the field selection for Quarter. So our expression should be:

Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Quarter = , Month = >} Sales)

Yet another inconvenient with the expression above.

When developing an application, you have to account for all the alternative selections the end user can make. So now, let’s assume the selection state is as follows: Year = 2010; Month = January; (Nothing selected in the field Quarter, but as we might expect, only one possible value (Q1).

If we want the sales for the previous month (December, 2009) we need to:

Ignore both Year and Month selections.

Create an expression that works just as the previous one, but with which we can also obtain the number 12 (for december) whenever the selection is January. (The previous expression (Max([Month (#)] – 1)) returns zero in this scenario, which is not useful.

Create an expression that returns the value for the previous Year. Simple task.

A direct approach would be to create an If Expression like this: If((Max([Month (#)]) – 1) = 0, 12, Max([Month (#)] – 1)). But I find it very impractical, so here is another approach:

In the script, assign a consecutive number for each month, a unique ID, one that you can easily play with. I use the following formula:

(Year(Date) – 1) * 12 + Num(Month(Date))

The above formula will for example, assign the value 24120 to December 2009 and 24121 to January 2010, and so on. Moreover, since these numbers are 5-digit long, you can easily find a more memory-friendly way to assign a unique ID to every month using the same concept. Our friend Barry, from qlikfix.com, recommended here to use the AutoNumber function. I second that, only if your calendar table is loading in ascending order based on the date, which is almost always the case.

So, now that we have the consecutive number for month (let’s call it MonthID), we can use this expression to retrieve the value for the previous MonthID that will work for every scencario:

Max(MonthID) – 1

The final expression

Our final expression will look like:

Sum({$<MonthID = {$(=Max(MonthID) – 1)}, Year = , Quarter = , Month = >} Sales)

Note that I’m ignoring Selection made on Year, Quarter and Month.

The same concept used in MonthID can be used for the field Quarter. We can easily and seamlessly create a lot of expressions for Point In Time Reporting after we’ve created the Calendar table with at least the following fields: Date, Year, Month, Quarter, MonthID and QuarterID.

More Examples

Now, lets see some common Set Analysis expressions that we can use when required:

YTD (Year-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},
Year = {$(=Max(Year))},
Quarter = ,
Month = >} Sales)

QTD (Quarter-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},
QuarterID = {$(=Max(QuarterID))},
Year = ,
Quarter = ,
Month = >} Sales)

MTD (Month-To-Date) Sales:

Sum({$<MonthID = {$(=Max(MonthID))},
Year = ,
Quarter = ,
Month = >} Sales)

Previous Month Sales:

Sum({$<MonthID = {$(=Max(MonthID) – 1)},
Year = ,
Quarter = ,
Month = >} Sales)

Previous Quarter Sales:

Sum({$<QuarterID = {$(=Max(QuarterID) – 1)},
Year = ,
Quarter = ,
Month = >} Sales)

Sales for the same Month but Previous Year:

Sum({$<MonthID = {$(=Max(MonthID) – 12)},
Year = ,
Quarter = ,
Month = >} Sales)

Sales for same Quarter of the Previous Year:

Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
Year = ,
Quarter = ,
Month = >} Sales)

YTD Sales for Previous Year

Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},
Year = {$(=Max(Year) – 1)},
Quarter = ,
Month = >} Sales)

Sales for Rolling 12 Months:

Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
Year = ,
Quarter = ,
Month = >} Sales)

So, there you have it, a complete set of formulas to help you create Point In Time Analysis in your QlikView Applications in a very simple fashion.

I hope you have enjoyed this Part IV of the Series ‘The Magic Of Set Analysis’. Next post: Create your Point In Time Reporting Set expressions once and reuse them in every new QlikView app.

As always, comments and feedback are welcome.

Mike.

Re: error when reloading the script

Fri, 16 Jan 2015 12:19:48 GMT

Hi Joe,

I think you right...its now looks fine.

thanks guys...thanks very much

Re: error when reloading the script

Peter_Cammaert — Fri, 16 Jan 2015 12:20:00 GMT

Correct, the first minus in the last expression isn't a minus (char(45)) but character code 150. Outside of the set of 128 regular ASCII characters

Re: error when reloading the script

Fri, 16 Jan 2015 12:24:11 GMT

no problem, glad to help

Re: error when reloading the script

Fri, 16 Jan 2015 12:32:43 GMT

hi Joe and other guys,

I have created the dates fields as per instructions but somehow is not populating the values .

LYTD....

Sum({$<MonthId = {“<=$(=Max(MonthId) – 12)”},Year = {$(=Max(Year) – 1)},Quarter = ,Month = >} Counter_Num)

YTD...

Sum({$<MonthId = {“<=$(=Max(MonthId))”},Year = {$(=Max(Year))},Quarter = ,Month = >} Counter_Num)

Re: error when reloading the script

Fri, 16 Jan 2015 12:38:25 GMT

sounds like a different question, now we are looking into your front end expressions it might be worth knocking up an small example application, would be easier to troubleshoot then

Re: error when reloading the script

Fri, 16 Jan 2015 12:41:14 GMT

yes I did by creating a chart n trowing the Monthid to see if it resolved to something but none.