You need to create a table with both create date and closed date as a single date. You can have an Event field which will tell you if it's closed or open.
Below is an example:
// Raw table
Load * inline [
// Create a new table for Open dates
// Start Date and End Date both correspond to single column (Date)
StartDate as Date, 'Open' as Event, 1 as Open_Flag
// Concatenate Closed Date info
Load ID, EndDate as Date, 'Closed' as Event,
// If EndDate is blank, Closed Flag = 0
if(trim(EndDate)='',0,1) as Closed_Flag
// Clean up
drop table Temp;
When loading data from DB, create a fact table in the format as below. Load the records with create date first. Then concatenate records with resolved date into the same FACT table. Now Date column in FACT table will have both Create Date and Resolved Date
In your chart, use the Date Field for x-Axis and count of Status for y Axis. Create separate expressions for different status that you need
Thanks all - this worked to get the basic info down, which the user approved.
Two things are still short, though.
1. Is there any way to color the space between the lines as red or green, based on which value is higher? this would go back and forth as more issues are created than closed and vice versa. (I can't create this in excel either.)
2. The sample chart has a value point for every date. so there could be a long string of dates where the data does not change (for example, a week of not creating any new issues), and the line is flat. the QV line has only a sloping line even though I have the continuous x-axis selected. I imagine I need to have a date record with zero issues. I've attached a spreadsheet with that example.
The current chart is good and thanks for everyone's help. I was just wondering if I can get these last two things.
QVCreatedVsResolved.xlsx 42.2 K
Check attached QVW if it is what you need. I have created a line chart for Created and Resolved and color coded each line.
Test.qvw 138.5 K