Coldfusion cfchart stacked order

Question

I have a couple of queries which pull data for use in a graph.

<cfquery name='clusterPrivateReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as msgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID=3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name='clusterNHSReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as msgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID<>3
GROUP BY organisationName, listSize
</cfquery>

The graph code is

<cfchart format="flash" title="Cluster referrals per 1000 patients from #dateformat(refRateStartDate, 'dd-mmm-yy')#" chartWidth="470" chartHeight="380" fontSize="12" style="chart.xml" seriesPlacement = "stacked" showLegend = "yes">
    <cfchartseries type="bar" seriescolor="##FFD800" seriesLabel="Private" query="clusterPrivateReferrals" valueColumn="msgCount" ItemColumn="organisationName">
    </cfchartseries>
    <cfchartseries type="bar" seriescolor="##F47D30" seriesLabel="NHS" query="clusterNHSReferrals" valueColumn="msgCount" ItemColumn="organisationName">
    </cfchartseries>
</cfchart>

this gives me the following graph

How do I get the data displayed sorted by the total of the stacked elements?

@ Ben

That got me on the right track, I didnt previously know QOQ could combine 2 completely different queries

<cfquery name='clusterPrivateReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as privateRate
FROM allReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID=3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name='clusterNHSReferrals'  dbtype="query">
SELECT organisationName, count(messageID)*1000/listSize as nhsRate
FROM allReferrals
WHERE datecreated>#refRateStartDate#
AND refTypeID<>3
GROUP BY organisationName, listSize
</cfquery>

<cfquery name="stackOrder" dbtype="query">
    select clusterPrivateReferrals.privateRate,
        clusterNHSReferrals.nhsRate,
        clusterPrivateReferrals.organisationName,
        (clusterPrivateReferrals.privateRate + clusterNHSReferrals.nhsRate) as totalRate
    from clusterPrivateReferrals, clusterNHSReferrals
    WHERE clusterNHSReferrals.organisationName = clusterPrivateReferrals.organisationName
    order by totalRate desc
</cfquery> 

Answer 1

The simplest way would be to use a QofQ:

<cfquery name="stackOrder" dbtype="query">
    select clusterPrivateReferrals.msgCount as privateReferrals,
        clusterNHSReferrals.msgCount as NHSReferrals,
        clusterPrivateReferrals.organizationName
    from clusterPrivateReferrals
    join clusterNHSReferrals on clusterNHSReferrals.organizationName = clusterPrivateReferrals.organizationName
    order by (privateReferrals+privateReferrals) desc
</cfquery>

I've not tested this, so you may need to tweak it a little.

Now, you should be able to use the two Referrals columns as the data columns for the graph.

Answer 2

Maybe add intermediate QoQ with filtering only by date? Something like this (can not test, so it may need some fixes):

<cfquery name='clusterCombinedReferrals'  dbtype="query">
SELECT organisationName, messageID, listSize, count(messageID)*1000/listSize as totalMsgCount
FROM clusterReferrals
WHERE datecreated>#refRateStartDate#
GROUP BY organisationName, listSize
</cfquery>

After that update your existing queries to include select from clusterCombinedReferrals and order by totalMsgCount at first place, also drop the filtering by date as it already applied.

Answer 3

I don't think you can without first outputting the queries to a structure and then sorting it and outputting the data from that with the cfchartdata tag inside each cfchartseries.

Something like this, maybe. I did this locally and it worked, but then I tried to convert the code to work with your queries and column names, so it might not work off a straight copy and paste. (But it might!) It also assumes that the length of the two queries will always be the same. If that isn't true, you might need to code around that.

<cfset data = {}>

<cfloop from="1" to="#clusterPrivateReferrals.recordCount#" index="x">

  <cfset structInsert(data, clusterPrivateReferrals["organisationName"][x], {})>

  <cfset data['#clusterPrivateReferrals["organisationName"][x]#'].private = clusterPrivateReferrals["msgCount"][x]>

  <cfset data['#clusterNHSReferrals["organisationName"][x]#'].nhs = clusterPrivateReferrals["msgCount"][x]>

  <cfset data['#clusterPrivateReferrals["organisationName"][x]#'].total = data['#clusterNHSReferrals["organisationName"][x]#'].private + data['#clusterNHSReferrals["organisationName"][x]#'].nhs>

</cfloop>

<cfset sorted = structSort(data, "numeric", "desc", "total")>

<cfchart format="flash" title="data" chartWidth="470" chartHeight="380" fontSize="12" seriesPlacement = "stacked" showLegend = "yes">

    <cfchartseries type="bar" seriescolor="##FFD800" seriesLabel="Private">
        <cfloop from="1" to="#arrayLen(datas)#" index="x">
            <cfchartdata item="#sorted[x]#" value="#data['#sorted[x]#'].private#">
        </cfloop>
    </cfchartseries>

    <cfchartseries type="bar" seriescolor="##F47D30" seriesLabel="NHS">
        <cfloop from="1" to="#arrayLen(datas)#" index="x">
            <cfchartdata item="#sorted[x]#" value="#data['#sorted[x]#'].nhs#">
        </cfloop>
    </cfchartseries>

</cfchart>