Excel Help - two sets of data for the x-axis in a line graph 2

[cjbrown815]

Excel 2010 - I have a simple line graph and I need to display/sort the x-axis first by date (all of the G column) and then by Quality Code (all of the E column). Basically two products come in per day and I need to seperate those. So I will have two data points per day.

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

hi,

Then you must have THREE columns: Col G is the CATEGORY axis values, plus two other columns for the two data points per day.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

yes, my Y-axis are the test results (I column)

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Actually, I did not posit a "yes/no" question.

So maybe post your SQL or your method of importing your test data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

SELECT SAMPLE.SAMPLE_ID, SAMPLE.SAMPLE_STATUS, SAMPLE.SUBMITTER, SAMPLE.SAMPLE_TYPE, SAMPLE.TEXT3, SAMPLE.TEXT5, SAMPLE.DATE3, SAMPLE.REMARKS, SAMPLE.LOT, SAMPLE.PRODUCT_LOT, SAMPLETYPE.ST_TYP, SAMPLETYPE.ST_ID, SAMPLETYPE.ST_NAME, SAMPLETYPE.ST_DESC, SAMPLEPARAM.PA_NAME, SAMPLEPARAM.TRESULT, SAMPLEPARAM.NRESULT, SAMPLEPARAM.SRESULT, SAMPLEPARAM.ORESULT, SAMPLEPARAM.SAMPLEPARAM_ID
FROM LIMSProd.dbo.SAMPLE SAMPLE, LIMSProd.dbo.SAMPLEPARAM SAMPLEPARAM, LIMSProd.dbo.SAMPLETYPE SAMPLETYPE
WHERE SAMPLE.SAMPLE_ID = SAMPLEPARAM.SAMPLE_ID AND SAMPLEPARAM.ST_N = SAMPLETYPE.ST_N AND SAMPLEPARAM.ST_VERS = SAMPLETYPE.ST_VERS AND ((SAMPLEPARAM.PRINT_FLAG=1) AND (SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND (SAMPLE.DATE3>=DateAdd(yy,-3,GetDate())) OR (SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND (SAMPLE.DATE3>=DateAdd(yy,-3,GetDate())) AND (SAMPLEPARAM.PA_NAME Like '%pH%') OR (SAMPLE.SAMPLE_TYPE Like 'WKLY_%') AND (SAMPLE.DATE3>=DateAdd(yy,-3,GetDate())) AND (SAMPLEPARAM.PA_NAME Like '%Salt%'))
ORDER BY SAMPLE.DATE3, SAMPLE.SAMPLE_TYPE

Y-Axis = SAMPLEPARAM.NRESULT
X-Axis = SAMPLE.DATE3 (I need to add SAMPLE.TEXT3)

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Example:

dte      val  point
9/1/2012 48.9     0
9/1/2012 15.7     1
9/2/2012 33.0     0
9/2/2012 36.7     1
9/3/2012 21.8     0
9/3/2012 42.9     1
9/4/2012 40.4     0
9/4/2012 15.2     1

PivotTable:

Sum of val  point	
dte            0     1
9/1/2012    48.9  15.7
9/2/2012    33.0  36.7
9/3/2012    21.8  42.9
9/4/2012    40.4  15.2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FredWagner]

Why not a two line graph - x axis is date, y-axis has two lines, one for the quality code for each product. Or make one a line graph, the other a vertical bar graph.

Fred Wagner

 

[SkipVought]

because...

So I will have two data points per day.

as I understand it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

Skip. I'm trying to do a list like you did. Sorry, here's the best I can come up with. I need the horizontal axis to show the survey date with the two quality codes above it and the line graph will show data points for each quality code. The data points represent a test result. The test parameter (pH, Butterfat, Salt) is displayed on the vertical axis in graduations of 5.

Survey Date
9/12/2012

Quality Code
1234
5342

Result (data pionts)
44.91
45.99


BTW, Thanks for hanging in there with me. You are a programmer and I am not, the communication gap is apparent

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Please post an example of your source data (query resultset), pared down to the relevant fields & values that correspond to the above posted result example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

Survey Date (SAMPLE.DATE3)
9/12/2012 (DATA)

Quality Code (SAMPLE.TEXT3)
1234 (DATA)
5342 (DATA)

Result (data pionts) (SAMPLEPARAM.NRESULT)
44.91 (DATA)
45.99 (DATA)

Not sure what else you're asking for

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

WHERE DID THESE VALUES COME FROM???

Certainly they do not look that what you posted, on your sheet!!!

I'm just GUESSING, just using some 'Scott Walker smarts', that SOME sheet in your workbook has data like this...

SAMPLE.DATE3   SAMPLE.TEXT3  SAMPLEPARAM.NRESULT
   9/12/2012           1234                44.91
   9/12/2012           5342                45.99

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FredWagner]

What you actually need is a graph where the entries on the X-axis are BOTH the date and the Quality codes, and the Y-axis are the result entries. We used to do graphs like this for the CincSAC, but it was a custom graphing program written on Tektronix workstations, not a spreadsheet tool.
The Tektronix box had 55K of available RAM, and single-character variable names, so it would be fairly easy to replicate in almost any flavor of basic that could talk to a graphics screen.
You could show the date and the Quality Codes in stacked cells below the graphy, and the results as bars or icons or whatever above each stack. Since we have color available, you could use a distinct color background on each Quality code, and a corresponding color on the line or bar with the data above it.

Fred Wagner

 

[cjbrown815]

Skip, good guess, thats exactly what the data looks like on the spreadsheet

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

well cj, you just changed the requirements by adding a set of independent labels. You'll need VBA code to accomplish that.

2 data points per date is no problem, as I previously suggested a very quick solution on 11 Sep 12 13:08.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

Thanks Fred,
I need both parameters to show on the graph Survey Date and Both quality codes for each date. The problem I'm having is trending the data when I get two samples on that day. The graph will show 8/30/12 and then 8/30/12. I need it to show 8/30/12 QC 1234 and 8/30/12 QC 2468 with the coresponding test values in the body of the graph.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[FredWagner]

You could append a -1, -2 to the date entries where you have more than one sample per day. If this is a manual setup with a limited number of entries, this will work.
Can you set it up so the entrie on the X-axis for each data set looks like
08/30/12-1 8/30/12-2
QC1234 QC1234
QC2468 QC2468

or, if the Quality codes are always the same, you could put a legend separately on the char, showing a color code for each code, and just color the data points to match their codes. That way you'd ONLY have the dates on the X-Axis. If you do the chart for a month, that could also be in the legend, and all you'd need would be the numeric days on the X-axis (with suffixes for multiple entries on tone day).

This is starting to sound familiar - back in the last century, I wrote a custom program for a company that produce agricultural feed supplements, so you could customize the butterfat content of milk produced, by adding supplements to tailor the nutritional content of what the farmer had in his feed silo. It even included a sequence for the mixing wagon to follow!

Fred Wagner

 

[cjbrown815]

Thanks Fred,
I just need to find a formula or something to add that second category (Quality Codes) to the horizontal axis. I tried several times using colon, semi-colon, "&" , comma...nothing works when I select the data . I can manually change the column headers and that shows up on the graph when I do that (I'd like a permanent solution but don't know VBA).


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[FredWagner]

could you add a second set of column headers, that would run parallel with the first ?

Fred Wagner

 

[cjbrown815]

If I do that I'm getting the excel version of a date. I did =E:E & "-" &H:H and it returned 1215802-40913, the first part is the quality code.


-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931