Stacked bar charts - do I need to translate data?

[#1]

Hi everyone,

I have a simple table:

Column A - Country

Column B - Year

Column C-F - Account1/Account2/Account3/Account4

I want to create a stacked bar chart, with a stacked bar each year for columns C-F. I can't figure out how to do it, because usually my data tables have the years as columns. Does anyone know:

a) how I can build a chart with the data table in its current structure,

b) if the data table needs to look different, what function can I use in the load script to restructure the table each time I reload?

Thanks!

[phaneendra_kunc]

Try Cross table Wizard.

[stevedark · Accepted solution]

Or for a simple table like that it is easier to key the CROSSTABLE statement rather than use the Wizard.  The syntax is:

CROSSTABLE (Account, Value, 2)

LOAD

   Country,

   Year,

   Account1,

   Account2,

   Account3,

   Account4

FROM ... your data source...

When this loads the first two columns are columns in the data set (this is denoted by the third parameter of '2').  The additional columns are then loaded with a row for each column, each with a dimension of 'Account' and a Value field.

You can then create a bar chart with dimensions of Year and Account, and an expression of Sum(Value) and tick the box to create it as a Stacked Bar.

Hope that helps,

Steve

[#4]

This worked perfectly! You explained that in a way that was easy to understand - thank you.

My next simple question would be: Accounts1-4 are now rows in one column. But what if I wanted my stacked bar chart to only display Accounts1 and 2? How would I write the expression to only include them? And if I wanted account 4 to be a line chart on a secondary axis - how would I write the expression for that?

Thanks again!

[phaneendra_kunc]

Use combo chart.

For accounts 1 &2 use this as an expression.

sum({<Account = {'Account1','Account2'}>} value). ----- select bar for this one

For account 4 use below expression and make that as line.

sum({<Account = {'Account4'}>} value)

[stevedark]

Thanks for your feedback Andrew.

Phaneendra is spot-on with regards to the Set Analysis to give you two accounts as bars and one as lines.  The only thing I would add is if you want the account types selectable (eg. in a list box) you should add an asterisk to the expressions:

sum({<Account *= {'Account1','Account2'}>} value)

sum({<Account *= {'Account4'}>} value)

Otherwise selections in the Account field will not be taken into account in your chart.

Steve

[#7]

Phaneendra and Steve, thank you! Phaneendra, the set analysis expression worked perfectly. And Steve, I had just run into that problem with list boxes and it was very confusing. Thanks for the simple explanation!

You guys are lifesavers.

Best,

Andrew