Distribution stack chart

Overview

Dates are represented by cells stacked on each other which create a distribution chart. Whole chart can be then colored by specific rules.

How to create a Distribution stack chart?

  1. Add Formula analysis:
    • Create brackets with if() formulas e.g.;
      if(fx:s1<0, year(), null())
      if(fx:s1>=0 & fx:s1<20, year(), null())
      if(fx:s1>=20, year(), null())
  2. Add Cross sampling:
    • Add Calculations 'Last' and enough Nth last values to cover the elements + 2-3 spares.
    • Create group with bracket-series.
  3. Add Transpose to flip data. Include only 'Nth las values' for Brackets group.
  4. Add Bar chart.
    • Open Graph layout (Ctrl+L). Mark brackets series and use 'Add new Text column' > 'One column per series per value'.
    • To remove all n/a go to Column defaults tab > Default cell text > Edit, paste in:
      {s .Value -m " "}
    • Go to Chart properties > Appearance > check 'Reversed' to flip chart.
    • Go to Chart properties > Chart elements > Rules to apply colors.

Examples

New houses

See the distribution of years (with date) by number of starts of new house construction.

Equity return

We show here distribution of years by S&P 500 index YoY growth rates. Years were divided into four groups and colored to make it easier to read the chart.

Tags: bardistributionstacked