Start and end dates connection in master calendar

[nicolai_moller]

Hi

I have a list of activities that each contains a startdate and an enddate.

I'm trying to implement a master calendar, but I can't figure out how to connect

the year field in the master calendar to both the startdate year and enddate year.

In my year listbox I can select a year but it only shows activities with enddates in that year,

where I want it to show all activities that has either a startdate or enddate or both, in that year.

Thanks.

[alexandros17]

in the script you have to write

if(year(startdate)=year(enddate), year(startdate), choose the year you prefer or a dummy value as 0) as year

hope it helps

[calvindk]

Calendar:

Date,

Date as EndDate,

Date as StartDate

Activities:

EndDate,

StartDate

Pick only DATE from the listbox

[RedSky001]

I would use intervalmatch() for this...

See the attached example.  Also the code here.

LET vDateMin = Num(MakeDate(Year(Today())-1,1,1));   // 1 year back from start of  this year

LET vDateMax = Num(MakeDate(Year(Today())+1,12,31));          //1 Years forward from end of this year

Calendar:

LOAD

          $(vDateMin) + RowNo() - 1                     AS DateNum

          ,Date($(vDateMin) + RowNo() - 1) AS Date

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

TEMP:

load *

,RowNo() as ID;

load * inline [StartDate, EndDate

01/01/2012, 10/01/2012

18/03/2012, 19/05/2013

];

IntervalMatch_Temp:

IntervalMatch ( Date )           load StartDate, EndDate  Resident TEMP;

IntervalMatch:

load StartDate & EndDate as DateRangeKey , Date

Resident IntervalMatch_Temp;

drop table IntervalMatch_Temp;

FACT:

load

StartDate & EndDate as DateRangeKey

,ID

Resident TEMP;

          join (FACT)

          load

                    DateRangeKey

                    ,Date

           Resident IntervalMatch;

drop tables IntervalMatch;

drop field DateRangeKey;

[nicolai_moller]

so instead of the inline load I would write:

load

MyStartDate as StartDate,

MyEndDate as EndDate

Resident MyTable;

?

[nicolai_moller]

so instead of the inline load I would write:

load

MyStartDate as StartDate,

MyEndDate as EndDate

Resident MyTable;

?

[RedSky001]

Yes.  Upload a .qvw with some of your data if you'd like me to modify it.

[nicolai_moller]

Here is a sample of my data.

If I select 2014 as year, activity C and D should be associated with 2014.

Selecting 2011 should be associated with activity B and C, even though B

doesn't have a date in 2011, but activity B is ongoing in 2011.

Thanks.

[RedSky001]

See V2 attached. 

I had to change some of your data from '03-29-2014' to '29-03-2014' to match your DateFormat='DD-MM-YYYY'....

Please mark this as correct if it helps.

[nicolai_moller]

I can get the document to reload but activities with a NULL (sagnavn B) is still grey when 2014 is selected 

This is actual dates from the souce data:

sagnavn	startdatotid	slutdatotid
A	2012-06-14 00:00:00.000	2014-12-31 00:00:00.000
B	NULL	2014-07-11 00:00:00.000
A	2013-09-23 00:00:00.000	2014-02-06 00:00:00.000
A	2010-02-04 00:00:00.000	2014-12-31 00:00:00.000

I have uploaded the document also.

Thanks