Question about Calander

[#1]

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();

[#2]

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

[#3]

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

[#4]

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

[#5]

Hi,

The data comes from a .xls

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

Regards

[#6]

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.

[#7]

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?

[#8]

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:

A:

load .....  file.xls;

join date

     Year(date) as Year

resident A;

[Anonymous]

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.