R:Charts will certainly process the zeroes correctly if "they're there".
Did your JOIN include an additional WHERE criterion in the second SELECT
statement: AND (COUNT(T_1.Ref_ID)) = 0
Bruce Chitiea
SafeSectors, Inc.
909.238.9012 mobile
------ Original Message ------
Sent: 2/2/2016 1:16:41 PM
Subject: [RBASE-L] - R:Chart (9.5); Bars In Series Not Lining Up
From: "Zeb Zbranek" <[email protected]>
To: [email protected]
Cc:
When I prepare a chart for a single year everything works fine. When I
try to combine two or more years the bars in the chart do not line up
with the proper category, because in some years I had a zero. In my
Views the zero's are not showing.
The View is two tables combined; 1)AccountsInfo and 2)ReferralCode. The
common link between the two tables is a two letter code that indicates
how the customers were found in a particular year. The view code is as
follows:
SELECT T_2.Ref_ID,T_2.ByYear,T_1.Ref_Category,(COUNT(T_1.Ref_ID))
FROM Referrals T_1, Aracount T_2
WHERE ( T_1.Ref_ID = T_2.Ref_ID AND ByYear = 2013 )
GROUP BY T_2.ByYear, T_1.Ref_Category, T_1.Ref_ID, T_2.Ref_ID
The view generates the following table:
T_2.Ref_ T_2.ByYear T_1.Ref_Category (COUNT(T_1
-------- ---------- -------------------- ----------
AT 2013 Attorney 4
FR 2013 Friend 16
IN 2013 Internet 6
OT 2013 Other 1
PH 2013 Phonebook listing 2
RP 2013 Repeat Client 20
SR 2013 State Rep. Related 1
WI 2013 Walk In 3
The problem is that not every referral code gets used every year. I need
it to add the following:
RA 2013 Radio 0
AD 2013 Court Appointed 0
If the view did this, then my bar graphs in R:Chart would all line up
perfectly and be beautiful. For additional information the following
CROSSTAB works perfectly:
R>CROSSTAB COUNT Ref_ID FOR Ref_ID BY ByYear FROM Referral_Sources
ByYear | AD AT FR IN OT PH RA
RP SR WI (Total)
---------- | -------- -------- -------- -------- -------- --------
-------- -------- -------- -------- --------
2010 | 0 6 7 10 3 4
0 9 0 3 42
2011 | 0 12 11 7 1 4
0 7 2 2 46
2012 | 0 8 17 7 2 2
0 4 1 2 43
2013 | 0 4 16 6 1 2
0 20 1 3 53
2014 | 1 2 20 22 1 1
1 17 1 9 75
2015 | 0 3 30 15 3 3
0 19 0 10 83
2016 | 0 1 2 2 0 0
0 0 0 0 5
---------- | -------- -------- -------- -------- -------- --------
-------- -------- -------- -------- --------
| 1 36 103 69 11 16
1 76 5 29 347
My goal is a bar graph for each year for each Ref_ID that would visually
show the above. I would not need the (Total) column.
I've tried the JOIN command, but cannot get the zeros to appear in my
View. Any suggestions?
Zeb Zbranek
Zbranek Firm, P.C.
1937 Trinity Street
Liberty, Texas 77575-2050
Telephone: 936-336-6454
Facsimile: 936-336-6039
The information contained in this message from Zbranek Firm, P.C. and
any attachments are confidential and intended only for the named
recipients. If you have received this message in error, you are
prohibited from copying, distributing or using the information. Please
contact the sender immediately by reply e-mail and delete the original
message.
