topic Question about Calander in QlikView

Question about Calander

Mon, 23 May 2011 09:40:48 GMT

Hi There,

I'm a little confused...

Maybe u can help me!

I made a calander. I made a basic table in Qlickview "year" from the calander.

Next to the table I've made a pivot with users and issues and the beloing dates.

When I select a year for example 2009 I want to see the same year in the Pivot table(this is just for testing).

But when I select a year nothing in the other table get selected.

Can someone please! help me...

This is my calander;

LET Start = floor(YearStart(AddMonths(today(), -12*3)));

LET End = floor(YearEnd(AddMonths(today(), 12)));

LET NumOfDays = End - Start + 1;

Date_src:

LOAD

$(Start) + Rowno() -1 as [Datum Aanmelding]

AUTOGENERATE $(NumOfDays);

Calendar:

LOAD

[Datum Aanmelding], // just in case

date([Datum Aanmelding]) as Date, // it will be in format defined in your SET DateFormat=, or in your system format

day([Datum Aanmelding]) as Day,

week([Datum Aanmelding]) as Week,

month([Datum Aanmelding]) as Month, // simple month name; it is dual - numeric and text

dual(month([Datum Aanmelding]) & '-' & year([Datum Aanmelding]),

year([Datum Aanmelding]) & num(month([Datum Aanmelding]), '00')) as MonthYear, // Month-Year format, dual

year([Datum Aanmelding]) as Year,

weekday([Datum Aanmelding]) as Weekday,

'Q' & ceil(month([Datum Aanmelding])/3) as Quarter, // in format Q1, Q2, Q3, Q4

dual('Q' & ceil(month([Datum Aanmelding])/3) & '-' & year([Datum Aanmelding]),

year([Datum Aanmelding]) & ceil(month([Datum Aanmelding])/3)) as QtrYear // Qn-Year, dual

// and whatever else you may want here...

RESIDENT Date_src;

Drop Table Date_src;

LET Start = null();

LET End = null();

LET NumOfDays = null();

Question about Calander

Mon, 23 May 2011 10:17:35 GMT

Hi,

I'm not sure if I have correctly understood your problem. You have created a Calendar table and I think you have another one with user's issues.

If you want to filter user's issue data using the calendar data, it is neccesary to make a relation between both tables. For doing this, use the same name for issue's date field (in user issues table) and the correspondant one in Calendar

Question about Calander

Mon, 23 May 2011 10:26:01 GMT

Hi,

Thanks for the reply.

I used the same column and I also see the link.

Situation;

table 1

user and date column.

table 2

Year

When I select a year for example 2010 then nothing is selected in table 1. When I look into the date column I will see the year 2010..

Question about Calander

Mon, 23 May 2011 11:13:47 GMT

Add another column to table1 with the year:

table 1:

user date Year(date) as Year

table 2:

Year

This will match correctly both tables

Question about Calander

Mon, 23 May 2011 11:32:29 GMT

Hi,

The data comes from a .xls

Do I have to add the column in the sheet or in the script?

Regards

Question about Calander

Mon, 23 May 2011 11:37:11 GMT

I would add it in the script, but maybe it is a bit more difficult. Try in a first step to add the field in the xls sheet, and if it works fine, the addapt the script for calculating the value from the data read.

Question about Calander

Mon, 23 May 2011 12:03:34 GMT

Hi,

I put it into the xls and it works fine. I now want months and q's I think it is better to do this into the script.

Wheer do I have to ad this? Into the calander or into the xls script?

Question about Calander

Mon, 23 May 2011 12:12:41 GMT

You have to add it to the script just after loading xls data. If this data is loaded into a table "A", you only have to make a join to that table in the following way:

load ..... file.xls;

join date

Year(date) as Year

resident A;

Question about Calander

Anonymous — Mon, 23 May 2011 13:24:41 GMT

Hi Marco,

Just to make sure (because the calender can be tricky) when you look at you table view (CTRL+t) do you see both tables connected on the Date field?

If so make a Table box and select [Date] and [Datum Aanmelding]

These 2 rows should match. If they dont, you have to take a closer look at the date format your data source.

If they do match please share an example of your Excel sheet then we can have a closer look.

Succes!

Dennis.