Using a variable to switch between x-axis values, not aggregating on bar graph

I'm trying to create a card where a user can switch between different aggregation groups for their x-axis. So if they select "Race/Ethnicity" they get something like this:

and if they select gender:

I successfully set up the variable:

And the BeastMode referencing the variable:

But when I try to graph using that BeastMode, the graph won't aggregate the groups correctly:

I figure it has something do with my sort, so I've tried sorting using the BeastMode itself. I tried creating a custom sort based on the Variable:

case
when NPS Grouping='Race/Ethnicity' then COUNT(DISTINCT Response ID)*(-1)
when NPS Grouping='Gender' then COUNT(DISTINCT Response ID)*
(-1)
when NPS Grouping='District' then District
when NPS Grouping='Cohort' then Cohort
when NPS Grouping='Cohort Year' then Year number
when NPS Grouping='Fiscal Year' then Fiscal Year
END

I tried using a generic aggregating BeastMode (total responses), but none of them will get the bars to aggregate.

My data table does aggregate correctly:

Any ideas on what else I could try?

Tagged:

Answers

  • @DavidChurchman I'd suggest removing your sorting for now and looking at the the beast mode in your Y-Axis. How are you aggregating your measures?

  • I removed the sort, and the y-axis is COUNT(DISTINCT Response ID)

  • Sorry, the y-axis is actually this;

    sum(ifnull(Promoter,0))/count(DISTINCT Response ID)-sum(ifnull(Detractor,0))/count(DISTINCT Response ID)

  • @DavidChurchman I don't see anything in your beast mode that would be causing the issue. Have you changed anything under the Trellis settings?

  • My BeastMode wasn't shared to the dataset before. I made some progress by sharing the BeastMode to the dataset. Not sure why that would make a difference, but I'm closer now. Thanks for helping, @MichelleH