count graph 1

[codrutza]

Hi
I am using CRXI on SQL Server.
I am building a graph, linear.
I want to show for change of month:
@import: if type=import then job
@export: if type=export then job
@domestic: if type=domestic then job
all jobs

I have just 1 table. I tryed to use count on the graph but is not working (I don't know why???). It shows me the total 408 jobs for January. The 4 lines on the graph are all showing the totals/month. ( I am using month(date))

Then I changed the summary operation in Distinct count. Now it's better, but in the graph I get 383 imports instead of 382, 15 exports instead of 14, 15 domestics instead of 12.(for January). It looks that in the graph an extra job is added for each change of type???

I think I don't know how to use distinct count or something, because I had problems with distinct count before.

I would be happy with any advise, please

Thank you

 

[lbass]

If you use a distinctcount of a conditional formula, any values that don't meet your condition will be counted as 1, so it will be off by one--unless all values meet your criterion, in which it will be correct. Since you can't predict this, you can't just subtract one from the summary.

I'm not sure I'm following but why couldn't you use {table.type} type as a second "on change of" field, and then use distinctcount as your summary? If you have other types you want to omit, select {table.type} in the chart expert->order->specified order->add the types you want to include->others tab->discard all others.

-LB

 

[codrutza]

Thank you
It is working for example for imports,if I put type in 'on change of', but I want to see in the same graph the imports, exports, domestics and total and does not allow me to do this.
After I put the type in on change of I got the message

The data you have selected does not fit the chart type.....

It's working for one job type but not for all in the same graph

 

[lbass]

You should be using a line chart, with two on change of fields: {table.date}(print on change of month) and {table.type}, with distinctcount of {table.jobID} for the summary. You would also use specified order on the type field if you want to exclude certain types. This will result in three lines, one for each month, showing per month along the x-axis.

If you also want a total line, you need to create a separate line chart, using date (as before) and {@total} as the on change of field, and distinctcount of job ID as the summary. The formula {@total}for the on change of field would be:

distinctcount({table.jobID})

You would then need to change the scale on your initial chart by selecting the y-axis values->right click->data(Y) axis options->scales->change the maximum value to match the second chart (you should probably also manually set the scale on the second chart). Then on the second chart, make everything transparent except the total line. Remove the legend and resize the chart so that the line corresponds to the lines in the first chart. Then overlay the second chart on the first.

-LB