Plotting a stacked graph over multiple fields

[geoffreyd]

Hi,

I have some data that looks like:

id,quintile_trait1,quintile_trait2,...,trait8
a,5,5,...,5
b,4,2,...,5
c,5,3,...,5

I'm attempting to create a stacked bar/rect plot showing the break down of each trait, like this: quintile

My attempts so far have got things heading in the right direction:

Chart Spec

// This is merged into the dashboard spec
{
      plot: [
        ...(['quintile_trait1', 'quintile_trait2', ..., 'quintile_trait8'].map((field, i) => {
            return {
              mark: 'barY',
              name: field,
              data: {
                from: 'traits_dashboard',
                filterBy: '$brush',
              },
              x: { sql: `'${field}'` },
              y: { count: null },
              sort: { y: { count: null } },
              fill: field,
              inset: 1,
            };
          })
        ),
        {
          select: 'toggleColor',
          as: '$brush',
        },
        {
          select: 'highlight',
          by: '$brush',
          opacity: 0.2,
        },
        {
          select: 'nearestY',
          as: '$hover',
          channels: ['y'],
        },
      ],
      colorScheme: 'Greens',
      colorScale: 'quantile',
      colorTickFormat: '+f',
      xDomain: '$traitsDomain',
      xTickRotate: 30,
    };

But each time I've hit hurdles, such as the above plot has 3 issues:

1. I can figure out how to get the stacks to always go from 1 - 5
2. Only the last bar is selectable
3. adding this plot to my dashboard makes selections on the other plots error:

Error: Binder Error: column "raw_trait1" must appear in the GROUP BY clause or must be part of an aggregate function.

I have also attempted to use use an fx channel (which I think would give an acceptable result), which gave me the right plots, but they weren't stacked, and each had their own 1-5 xDomain. (I can't seem to get back to that state to provide the code/image)

I've also attempted to unpivot the data

UNPIVOT 'traits_dashboard'
ON COLUMNS('quintile_.*')
INTO
    NAME trait
    VALUE quintile;
)

to make:

id,trait,quintile 
a,quintile_trait1,5
a,quintile_trait2,5
a,quintile_trait8,5
b,quintile_trait1,4
b,quintile_trait2,2
b,quintile_trait8,5

But I don't think this is what I want with how it will effect my other plots with the cross filter selections and counts.

Any guidance as to how to approach this challenge? and if I need to make completely seperate plots for each trait, and try and squish them together, how would I best make a single column stacked bar/rect?

Also, In the future I have a requirement for a spider/radial plot across the averages for these categories as well, so general guidance on how to work with fixed sets of fields would be very helpful.

Thanks.