How to break this down for use in a graph?

[Michael Modic]

Hi,

I have been trying for many hours to figure out how to do this, and have
yet to come up with a solution. It's my lack of experience I'm sure and
I hope someone can point me in the right direction.

I have an ActiveRecord resultset that contains rows from a query with
group by clauses. I'm trying to generate a stacked column graph that
will show the number of orders for a period of time stacked by the rep
that generated the orders broken down by date.

The AR result set has the following attributes:

sale_date (date the sale was made - for a single date, there will be a
row for each each rep that had a recorded sale on that date)
sales_rep (the rep - the name of the rep that made the sale on the sale
date)
order_count (the count of orders made by the above rep for the sale
date)

The graph expects an array of dates for the axis. I did:

sale_days = @results.map{ |item | item.sales_day }.uniq

to get a unique set of dates (as they repeat for each rep, but I only
want the unique dates).

I don't have a clue how to get something that allows me to do this:

sales_graph.add( :series, "Sales Rep Name", [a, b, c, d, e] )

where the sales rep name is found in my result set, and the a,b,c... is
the actual count of orders for that rep for each day. I need to do the
sales_graph.add... for each rep found in the resultset. I can handle
that part, if I can figure out how to get the counts for a rep into it's
own array/hash and tied back to the rep.

Please help! :-( If I need to structure my query differently, I can.
Ruby is so beautiful and I know I am missing some easy way to do this
and for the life of me I can't figure it out on my own.

Thanks for any pointers in advance,

M.

 

[Mark Thomas]

Please help!  :-(  If I need to structure my query differently, I can..

Ruby is so beautiful and I know I am missing some easy way to do this
and for the life of me I can't figure it out on my own.

It would help if you show your model or at least 'p @results'. Then
show what you want as output. For me at least, code will be easier to
decipher than what you mean by things like "tied back to the rep".

 

[Michael Modic]

It would help if you show your model or at least 'p @results'. Then
show what you want as output. For me at least, code will be easier to
decipher than what you mean by things like "tied back to the rep".

Sorry about that. Here's a sample of the results after calling
inspect. Should help clarify the structure of my resultset.

#<Client:0x4920884 @attributes={\"order_count\"=>\"4\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920848 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"John\"}>,
#<Client:0x492080c @attributes={\"order_count\"=>\"10\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Jane\"}>,
#<Client:0x49204c4 @attributes={\"order_count\"=>\"1\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920488 @attributes={\"order_count\"=>\"9\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"John\"}>,
#<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
#<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"

 

[Michael Modic]

\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920848 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"John\"}>,
#<Client:0x492080c @attributes={\"order_count\"=>\"10\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Jane\"}>,
#<Client:0x49204c4 @attributes={\"order_count\"=>\"1\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920488 @attributes={\"order_count\"=>\"9\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"John\"}>,
#<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
#<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"

I forgot to put what I wanted for output. With the results above as a
sample, I want:

sales_dates = [2009-04-13, 2009-04-14]

Then...

Michael, [4, 1]
John, [2, 9]
Jane, [10, 0]
David, [0, 7]
Albert, [0, 2]

or whatever it would take for me to iterate over an array/hash of
sales_reps and call something like the following:

for rep in sales_reps
sales_graph.add( :series, rep, [a, b] )
end

so that I would end up with:
sales_graph.add( :series, "Michael", [4, 1] )
sales_graph.add( :series, "John", [2, 9] )
sales_graph.add( :series, "Jane", [10, 0] )
sales_graph.add( :series, "David", [0, 7] )
sales_graph.add( :series, "Albert", [0, 2] )

I hope this helps. Again, any advice is greatly appreciated.

Regards,

Michael

 

[Rob Biedenharn]

#<Client:0x4920884 @attributes={\"order_count\"=>\"4\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920848 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"John\"}>,
#<Client:0x492080c @attributes={\"order_count\"=>\"10\",
\"sales_day\"=>\"2009-04-13\", \"sales_rep\"=>\"Jane\"}>,
#<Client:0x49204c4 @attributes={\"order_count\"=>\"1\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Michael\"}>,
#<Client:0x4920488 @attributes={\"order_count\"=>\"9\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"John\"}>,
#<Client:0x4920438 @attributes={\"order_count\"=>\"7\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"David\"}>,
#<Client:0x49203e8 @attributes={\"order_count\"=>\"2\",
\"sales_day\"=>\"2009-04-14\", \"sales_rep\"=>\"Albert\"}>]"

I forgot to put what I wanted for output. With the results above as a
sample, I want:

OK, so I'll start with @results having AR model instances like the
above.

# First, let's make it into a simpler data structure:
# a hash with keys of the sales_rep with values as an array of
# [sales_day, order_count] pairs.
sales_data = Hash.new {|h,k| h[k] = []}
@results.each do |client|
sales_data[client.sales_rep] << [client.sales_day,
client.order_count]
end

sales_dates = [2009-04-13, 2009-04-14]

# get the unique, sorted set of dates
sales_dates = sales_data.values.map{|pairs|pairs.map{|pair|
pair[0]}}.flatten.uniq.sort

# Perhaps you want to eliminate "holes" in the array of sales_dates

# Make sure each sales_rep has the same set of entries
sales_data.keys.each do |rep|
sales_data[rep] = sales_dates.map do |d|
if pair = sales_data[rep].assoc(d)
pair[1].to_i
else
0
end
end
end

Then...

Michael, [4, 1]
John, [2, 9]
Jane, [10, 0]
David, [0, 7]
Albert, [0, 2]

irb> pp sales_data
{"Michael"=>[4, 1],
"David"=>[0, 7],
"John"=>[2, 9],
"Albert"=>[0, 2],
"Jane"=>[10, 0]}
=> nil

or whatever it would take for me to iterate over an array/hash of
sales_reps and call something like the following:

for rep in sales_reps
sales_graph.add( :series, rep, [a, b] )
end

so that I would end up with:
sales_graph.add( :series, "Michael", [4, 1] )
sales_graph.add( :series, "John", [2, 9] )
sales_graph.add( :series, "Jane", [10, 0] )
sales_graph.add( :series, "David", [0, 7] )
sales_graph.add( :series, "Albert", [0, 2] )

I hope this helps. Again, any advice is greatly appreciated.

Regards,

Michael

--

sales_data.each do |rep,sales|
sales_graph.addseries, rep, sales)
end

Assuming that you have sales_graph defined somewhere up front.

-Rob

Rob Biedenharn http://agileconsultingllc.com
(e-mail address removed)

 

[Michael Modic]

Rob - thank you so much!!! I'll email you privately to make a small
contribution for your assistance. It seems so easy now after seeing the
solution, but I would not have got it on my own.

Regards,

Michael