Graph based on two queries

[pen81]

Hi,

I have produced a graph in CR10 connecting to a sqlite database used by our helpdesk software (Spiceworks).

For a six week period, it shows all tickets raised for each week as a bar on the graph (acheived by using "On change of" createDate and "Show Values" as a Count on the tables ID field).

I also have a seperate graph that uses "On change of" completeDate, so that it shows all tickets closed in the same 6 week period.

My question is, could I show both on one graph? I realise this involves two seperate queries and cannot be done using the simple chart expert, but can it be achieved any other way, maybe using crosstabs?

 

[lbass]

Are the create date and complete date two separate fields? Or is there only one field {table.date} with a status field that distinguishes the two?

-LB

 

[pen81]

They are two separate fields in the table

 

[lbass]

You could use a command as your datasource that combines the dates into one field, like this:

select 'Create' as datetype, table.createdate as datefld, table.ID
from table
union all
select 'Complete' as datetype, table.completedate, table.ID
from table

Then in the bar chart, add {command.datefld}(on change of week) as your first on change of field, {command.datetype} as your second on change of field, and distinctcount of ID as your summary field.

-LB