Hi, I've been asked to generate a chart that shows New reports and Corrected Reports by date. It would look like this:
Date
New Reports
Corrections
Total Reports
9/1/2010
0
10
10
9/2/2010
0
924
924
9/3/2010
0
52
52
9/4/2010
0
61
61
9/5/2010
43
872
915
9/6/2010
74
841
915
9/7/2010
151
907
1058
9/8/2010
217
742
959
9/9/2010
328
637
965
9/10/2010
39
86
125
9/11/2010
47
59
106
9/12/2010
435
469
904
9/13/2010
651
389
1040
9/14/2010
1132
292
1424
9/15/2010
796
234
1030
9/16/2010
78
18
96
9/17/2010
52
3
55
9/18/2010
85
19
104
9/19/2010
739
113
852
9/20/2010
845
102
947
9/21/2010
983
99
1082
The problem is the New Reports come from one table with the date it was received, and the corrections come from another table with the date it was corrected. The user would like to pick a calendar date and see the new reports and corrected reports for that day. I've been looking into date islands but can't seem to find the right approach. This is probably a common problem, but how can I get the the three dates to line up?
Concatenate the two tables, so that your resulting table has the Date, New and Corrected columns. In your chart you will have Date as a Dimension, then Sum(New), Sum(Corrected) and Sum(New)+Sum(Corrected) as Expressions. It is tempting to replace the Dimension with a Cycle Group that contains Year,Month,Date.
Thank you all for your help. I'm testing the various approacheds you all suggested. I'm still working on getting the date formats to match to make it work.
Michael, thank you. I used the date island approach. I had some problems with the dates in your other solution because the fields were time stamped with hours and minutes and I just couldn't get them to match.
Don, You can always convert timestamp to a date by cutting off the fraction part using floor() function: date(floor(YourTimeStampField)) as YourDateField
.png "Former Employee"){kind=icon}