bar chart query

[Former Member]

Hi,

I am pretty new to CR, so please excuse any ambiguity which i may use during this explanation.

I have two db tables as follows:

table1:

-

---

CustomerID

product

table2:

order number

customerID

purchased[yes/no]

I have linked these two tables by the order number field. I have created a bar chart that is currently displaying the customer number on the x axis and a count on the y axis.

For every customer number there are 2 horizontal bars. One bar is displaying the total number of orders made by that customer and the other bar shows the number of order purchased out of the total number of orders made.

<**> So if customer1 was to make 4 orders but only purchase 1, the first bar would rise to 4 and the second bar would rise to 1. (1 out of 4).

However, I dont want it like this. I want it so that if customer 1 makes 4 orders and purchases 1 order, then the first bar rises to 3 and the second bar rises to 1. (totalling 4).

Unfortunately because i am dealing with two db tables, I cant figure out how to get this to work for me.

I tried switching to a stacked bar chart as opposed to a side-by-side' bar chart, but what happens here is that, taking <**> as the same example, the single stacked bar would be split into 2 sections, lets say green and blue. The blue section would be 4 and the green section would be 1. (totalling 5 on the chart). Where i would want the blue section to be 3 and the green section to be 1 (totalling 4 - the correct figure)

Sorry for the lenght of the explanation, but i tried to explain it as best as i could.

Can anyone please advise on this issue?

Thanks

J

[former_member260594 · Accepted solution]

James,

Try creating a formula that takes the difference between the number of orders vs the number of orders purchases. For the show values fields of the chart insert the field that counts the orders purhcased and then the formula that takes the difference. Make this a stacked bar chart. The bars should equal the total number of orders broken out by purchased and not purchased.