Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
That sounds very sensbile, I think you'll find the data model will work then. Also I notice you have a synthetic key, you can drop the temp data to get rid of this.
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
Calendar:
Date,
Date as EndDate,
Date as StartDate
Activities:
EndDate,
StartDate
Pick only DATE from the listbox
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;
so instead of the inline load I would write:
load
MyStartDate as StartDate,
MyEndDate as EndDate
Resident MyTable;
?
so instead of the inline load I would write:
load
MyStartDate as StartDate,
MyEndDate as EndDate
Resident MyTable;
?
Yes. Upload a .qvw with some of your data if you'd like me to modify it.
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.
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.
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