Multi-Series not matching.

[Jamie 1]

Hello.

 

I have a Multi-Series chart based upon the FC Demo. I'm using columns to show 'volume' and a line to show 'Run-Rate'.

The chart is over 1 year and divided into Months, but there is not data for every month, hence I expect some gaps in the chart.

The 'volume' columns match up to the months good, but the 'run-rate' line does not recognise the gaps.

The MYSQL statement seems fine. Any help would be much appreciated, thanks.

 

Here's the code, the generated XML and a screenshot of the chart.

 

 

 

function getCumulativeSalesByCatXML($intYear, $forDataURL) {
 
        // Function to connect to the DB
        $link = connectToDB();
 
        //To store categories - also flag to check whether category is
 //already generated
 
 //Initialize XML elements
 //Categories
 $strCat = "<categories>";
 $arrCat =array();
 $strSQL = "SELECT  Month(o.date) as MonthNum from Months as o";
        $result = mysql_query($strSQL) or die(mysql_error());
        if ($result) {
  $mc=0;
        while($orsCat = mysql_fetch_array($result)) {
                //Add this category as dataset
   $arrCat[$mc++]=date("n",mktime(0,0,0,$orsCat['MonthNum'],2,1994));
   $strCat .= "<category label='" . date("F",mktime(0,0,0,$orsCat['MonthNum'],2,1994)) . "' />";
  }
        mysql_free_result($result);
 
 }
 $strCat .= "</categories>";
  
 
 
 //First we need to get unique categories in the database
 $strSQL = "Select prodnum as areaid, prodname as areaname from PRODUCTS GROUP BY areaid";
        $result = mysql_query($strSQL) or die(mysql_error());
  
   //Initialize datasets
 $strAmtDS = "<dataset seriesname='Volume'>";
 $strQtyDS = "<dataset seriesName='Run-Rate' parentYAxis='S'>";
  
        //To store datasets and sets
        $strDataXML = "";
 
        if ($result) {
        while($orsCat = mysql_fetch_array($result)) {
                //Add this category as dataset
                $strDataXML .= "<dataset seriesName='Volume'>";
    
    
                //Now, we need to get monthly sales data for products in this category
                $strSQL = "SELECT 
CASE WHEN MONTH(timemixstart)>=7 THEN
          concat( 'F',date_format((timemixstart),'%y')+1)
   ELSE concat('F', date_format((timemixstart),'%y')) END AS SalesYear,
   YEAR(timemixstart) as year,
Month(o.timemixstart) as MonthNum, p.prodnum as areaid, p.prodname as areaname, ROUND(SUM(o.mixact),0) as units,
ROUND (SUM(o.mixact / TIMEDIFF(o.timemixfin, o.timemixstart)*6000)/COUNT(o.mixact),0) As rate
FROM   PRODUCTS as p, PROD as o 
WHERE  o.mixnum = p.prodnum
GROUP BY  SalesYear, Month(o.timemixstart) HAVING SalesYear = 'F14'";
                //Execute it
                $result2 = mysql_query($strSQL) or die(mysql_error());
   $mc=0;
                while($ors = mysql_fetch_array($result2)) {
                //Append <category label=''> if not already done

//Generate the link
                $strLink = urlencode("javaScript:updateProductChart(" . $intYear . "," . $ors['MonthNum'] . "," . $ors['areaid'] . ");");
 
    while($arrCat[$mc++]<$ors["MonthNum"]){
        $strDataXML .="<set/>";
    }
                 $strDataXML .=   "<set value='" . $ors['units'] . "' link='" . $strLink . "'/>";
    
     $strQtyDS .="<set value='" . $ors['rate'] . "' link='" . $strLink . "'/>";
                }
                //Clear up objects
                mysql_free_result($result2);
                //Close dataset element
                $strDataXML .= "</dataset>";
    $strQtyDS .= "</dataset>";
    $strAmtDS .= "</dataset>";
        }
        }
        mysql_close($link);
 
 //Create full XML
 $strXML = $strCat . $strQtyDS . $strDataXML;
 
        //Return
 return $strXML;

}

 

 

 

 

<?xml version="1.0"?>

<chart legendPosition="BOTTOM" numDivLines="4" showValues="0" sFormatNumberScale="1" sYAxisName="Run-Rate" SYAxisName="Run-Rate" numberSuffix=" units" PYAxisName="Volume" formatNumberScale="1" animation="1" palette="2" XAxisName="Month" subcaption="(Click on a column to see monthly Run-Rate in the chart below this)" caption="Run-Rate / Volume for 2014"><categories><category label="January"/><category label="February"/><category label="March"/><category label="April"/><category label="May"/><category label="June"/><category label="July"/><category label="August"/><category label="September"/><category label="October"/><category label="November"/><category label="December"/></categories><dataset parentYAxis="S" seriesName="Run-Rate"><set link="javaScript%3AupdateProductChart%282014%2C1%2C14%29%3B" value="4073"/><set link="javaScript%3AupdateProductChart%282014%2C7%2C1%29%3B" value="4656"/><set link="javaScript%3AupdateProductChart%282014%2C8%2C1%29%3B" value="4552"/><set link="javaScript%3AupdateProductChart%282014%2C9%2C1%29%3B" value="4569"/><set link="javaScript%3AupdateProductChart%282014%2C10%2C1%29%3B" value="4160"/><set link="javaScript%3AupdateProductChart%282014%2C11%2C1%29%3B" value="4147"/><set link="javaScript%3AupdateProductChart%282014%2C12%2C1%29%3B" value="4092"/></dataset><dataset seriesName="Volume"><set link="javaScript%3AupdateProductChart%282014%2C1%2C14%29%3B" value="885966"/><set/><set/><set/><set/><set/><set link="javaScript%3AupdateProductChart%282014%2C7%2C1%29%3B" value="1744673"/><set link="javaScript%3AupdateProductChart%282014%2C8%2C1%29%3B" value="1610447"/><set link="javaScript%3AupdateProductChart%282014%2C9%2C1%29%3B" value="1698476"/><set link="javaScript%3AupdateProductChart%282014%2C10%2C1%29%3B" value="439920"/><set link="javaScript%3AupdateProductChart%282014%2C11%2C1%29%3B" value="1359675"/><set link="javaScript%3AupdateProductChart%282014%2C12%2C1%29%3B" value="1553473"/></dataset><styles><definition></definition><application><apply styles="CaptionFont" toObject="caption"/><apply styles="SubCaptionFont" toObject="SubCaption"/></application></styles></chart>

 

 

 

 

[Jamie 1]

I've now attached a screen of the XML, which shows the gap and lack of gap better.

The gap should be the same for both datasets.

 

By the way, I am trying to get results for the financial year, hence the tricky code.

Edited February 12, 2014 by Jamie 1

[Haritha]

Hi,

 

If you want the gaps to be shown for the Line also, then you need to specify empty datasets in its corresponding dataset.

 

Ref.

 

<dataset parentYAxis="S" seriesName="Run-Rate"><set link="javaScript%3AupdateProductChart%282014%2C1%2C14%29%3B" value="4073"/>

<set link="javaScript%3AupdateProductChart%282014%2C7%2C1%29%3B" value="4656"/>

<set/>

<set/>

<set/>

<set/>

<set/>

<set link="javaScript%3AupdateProductChart%282014%2C8%2C1%29%3B" value="4552"/>

<set link="javaScript%3AupdateProductChart%282014%2C9%2C1%29%3B" value="4569"/>

<set link="javaScript%3AupdateProductChart%282014%2C10%2C1%29%3B" value="4160"/>

<set link="javaScript%3AupdateProductChart%282014%2C11%2C1%29%3B" value="4147"/>

<set link="javaScript%3AupdateProductChart%282014%2C12%2C1%29%3B" value="4092"/>

</dataset>

 

Also, please use the attribute "connectNullData" and set it to 0. This attribute lets you control whether empty data sets in your data will be connected to each other OR will they appear as broken data sets.

 

Ref.

<chart ... connectNullData='0' ..>

 

Please find the screenshot of chart obtained after making the above said changes.

 

Hope this helps.

[Jamie 1]

Thanks very much for spending so much time on my problem.

I suppose I will need to work a LEFT JOIN statement to get the gaps?

 

I am trying to show the 'Financial Year', so need the chart to run from July - June,  not January - December.

 

Would you know how this could be done please? 

 

Jamie.

[Jamie 1]

I have managed to do this by creating another table just for financial year, so all good there thanks.

 

If you can figure out what the MYSQL code is to put the gaps into the 2 data sets, then that would be very helpful.

 

Thanks again.

 

Jamie.

[Haritha]

Hi,

 

Glad you could manage to do so yourself.

 

If you want to have blank set elements when your database does not return a value, then while forming XML data, you need to just append a blank set element to your XML string.

 

Eg.

xmlData+="<set />";

 

If this is not your requirement, then please let us know how you are forming your XML data to render a chart, so that we can help you better.

 

Hope this helps.