Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Question about Calander

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

8 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Hi,

The data comes from a .xls

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

Regards

Not applicable
Author

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.

Not applicable
Author

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?

Not applicable
Author

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
Not applicable
Author

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.