adding extra month's in Qlikview

[Anonymous]

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!!

[#2]

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

[Anonymous]

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

[Or]

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.

[#5 · Accepted solution]

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

[Anonymous]

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"?

[#7]

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

[Anonymous]

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

[Anonymous]

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

[#10]

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