topic Tricky date question in QlikView

Tricky date question

Mon, 04 Oct 2010 20:43:40 GMT

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?

The dates are

FLOOR_RCVD_DATE from New Reports

FLOOR_CORRECTED_DATE from Corrected Reports

CalendarDate from Calendar date island.

Thanks.

Don

Servicelink, Pttsburgh, Pa

Tricky date question

Anonymous — Mon, 04 Oct 2010 21:25:21 GMT

Don,
See my test application in a response in this thread, front end table "Example 1" - I think it is similar to your question.

Tricky date question

MichaelRobertshaw — Tue, 05 Oct 2010 07:25:28 GMT

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.

Tricky date question

Tue, 05 Oct 2010 16:29:27 GMT

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.

Tricky date question

Tue, 05 Oct 2010 20:56:42 GMT

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.

Tricky date question

Anonymous — Thu, 07 Oct 2010 18:22:48 GMT

Don,
You can always convert timestamp to a date by cutting off the fraction part using floor() function:
date(floor(YourTimeStampField)) as YourDateField