topic Re: adding extra month's in Qlikview in QlikView

adding extra month's in Qlikview

Anonymous — Wed, 17 Aug 2011 07:52:42 GMT

I have an Excel sheet with two month. However I want to add all the month's of the year and link them to quarters. (there are no Quaters in the source file)

So i've created an Inline in Qlikview to load extra month's and quarters. However when I reload and open the Month field, I see that jun and jul are double (one from the source and one from the calander table (that I created) in Qlikview. Futhermore the data is not linked. So if I click on jun I should see the Q3. However this is not the case. Plus Qlikview now sees "month" as a keyfield. That's not something I want.

How can I solve the above? Should I join the field "month" from CIS table with the one I created (the calander table)? If so, how can I do this?

Thanks for you help!!

adding extra month's in Qlikview

Wed, 17 Aug 2011 09:18:48 GMT

Hi,

try to solve it with an general calender.

LET varMin = Num('01/01/2008');

LET varMax = Num('31/12/2011');

date:

LOAD date($(varMin) + rowno() - 1) AS tempdate

AUTOGENERATE $(varMax) - $(varMin) + 1;

calender:

LOAD

tempdate AS date,

DAY(tempdate) AS day,

WEEK(tempdate) AS week,

MONTH(tempdate) AS month,

YEAR(tempdate) AS year,

'Q'&CEIL(MONTH(tempdate) / 3) AS quarter

RESIDENT date;

DROP TABLE date;

Then your month-field from the excel sheet should have the same name like in calender.

Regards

vicky

Re: adding extra month's in Qlikview

Anonymous — Wed, 17 Aug 2011 10:49:15 GMT

Hi,

Unfortunately it doesn’t work. I get the following error message:

Script line error:

Date:

LOAD date( + rowno() - 1) as tempdate

AUTOGENERATE - + 1

Table not found

Calender:

LOAD

tempdate AS date,

DAY(tempdate) AS day,

WEEK(tempdate) AS week,

MONTH(tempdate) AS month,

YEAR(tempdate) AS year,

'Q'& CEIL(MONTH(tempdate) / 3) AS quarter

RESIDENT Date

Thanks

Aissam

Re: adding extra month's in Qlikview

Or — Wed, 17 Aug 2011 11:09:53 GMT

Regarding the lack of association - one of your month fields appears to be inserting spaces. When using Trim() on the month name, I see the correct association, but when using the Month field as-is, I do not. Try running Trim() on the month name you are deriving and see if that fixes the association.

Regarding the key field - if you want to merge your Quarter field into the primary table rather than using a key, simply prefix your inline load with an OUTER JOIN statement (or RIGHT JOIN, which would work equally well in your case).

I do agree with vicky, though - it is best to have a master calendar table.

adding extra month's in Qlikview

Wed, 17 Aug 2011 11:11:12 GMT

Hi,

you have to change the dates into your format. I have not look at this, sry.

You have to write:

LET varMin = Num('1-1-2010')

LET varMax = Num('31-12-2011')

Then the generation works.

RowNo() is a function that counts the lines in the internal table. But this functions begins with line 1, not with 0.

So Qv is able to generate every day between the minimal date and the maximal date. More information to that function you get in the help.

Regards

vicky

Re: adding extra month's in Qlikview

Anonymous — Wed, 17 Aug 2011 11:35:02 GMT

Thanks!! The masterCalander now works. But there is no link between the dates of the CIS table and the Calander table . When I click on jul I don't see the results of jul. Can I solve this problem by doing a "outer join"?

Re: adding extra month's in Qlikview

Wed, 17 Aug 2011 12:26:43 GMT

Hi,

Add in you table the line: Date(Date#(DEP, 'DD-MM-YYYY') 'DD-MM-YYYY') AS Date.

That is the linking.

Maybe you have to rename the Month, ect because you get synthectic keys.

vicky

adding extra month's in Qlikview

Anonymous — Thu, 18 Aug 2011 13:07:59 GMT

Hi Vicky,

I've added the date line. But that didn't solve the problem. When I click on June, all the results dispear.

I just added an extra row in the source file (Excel) to load the Quarters.

Thanks for your help!!!

iSam

Re: adding extra month's in Qlikview

Anonymous — Thu, 18 Aug 2011 13:08:52 GMT

Hi Vicky,

I've added the date line. But that didn't solve the problem. When I click on June, all the results dispear.

I just added an extra row in the source file (Excel) to load the Quarters.

Thanks for your help!!!

iSam

Re: adding extra month's in Qlikview

Wed, 28 May 2014 12:02:49 GMT

Hi,

That was interesting Post.

Can any suggest me how to create Month end for all Months such that there will be only one dimension Month field and one measure Sales.

Totally we need to get 24 bars(If we show that in bar chart)like

Jan Month end,Feb Month end...Dec, Month end