Stacked bar with 2 dimensions and 2 expression

[#1]

Hi all,

I want to build a stacked bar chart, which shows Demand vs Supply. Demand will be the overall number for a month but supply will be broken down by departments per month.

I need to show a stacked bar chart per month, where the supply is broken down by department and the difference between the overall demand and overall supply at the top of it.

I am attaching the sample data file and sample qvw. I am able to achieve part of it using valuelist but the demand and supply are being broken into two different bars (see chart named Demand vs Supply). Is there anyway I can stack the gray bar on top of the color bars?

Any help would be greatly appreciated.

Thanks!

[#2]

I was able to achieve the expected output by doing some changes on the script. I was not able to achieve this without changes in the script. If any of you were able to achieve it without changes in script please post your ideas.

Thanks!

[johnw · Accepted solution]

I used script changes too, but without any calculations in the script if that matters at all. The sum(if()) means low performance on large data sets, but that shouldn't be an issue if these are just monthly totals by department. I set up a field that has all departments plus 'Excess Demand'. Then I reference that field when deciding what to add up for that "department".

DemandVsSupply:
LOAD text(fieldvalue('Department',recno())) as Department2
AUTOGENERATE fieldvaluecount('Department')
;
CONCATENATE (DemandVsSupply)
LOAD 'Excess Demand' as Department2
AUTOGENERATE 1
;

if(Department2='Excess Demand',sum(Demand)-sum(Supply),sum(if(Department=Department2,Supply)))

You CAN do it without that script change with a valuelist replacing Department2, but I wouldn't. Still, if you really, really don't want to change script, I'll include that approach as well. Everywhere the above uses Department2, use this instead.

valuelist($(=chr(39)&concat(distinct Department,chr(39)&','&chr(39))&chr(39)&','&chr(39)&'Excess Demand'&chr(39)))

[#4]

Your first solution worked like a charm! Thanks a lot.

[johnw]

Here's another approach with higher performance than the previous two without doing any calculation in script. I concatenate the two tables using 'Excess Demand' as the Department for demand. Then the chart has this expression:

if(Department='Excess Demand',sum(Demand)-sum(total <YearMonth> Supply),sum(Supply))