Dynamic series line bar

LCash

It's a regular frustration for me that I can't create a line bar graph which stacks dynamically based on a dimension without using multiple beastmodes.

For example I want to include a target value as a line, and then actuals stacked by type over time. This has been raised before here: https://dojo.domo.com/discussion/comment/23168#Comment_23168)

The best I can do is a grouped bar using categories:

But this is quite ugly and difficult to read.

Anyways, just wanted to raise the question again in case I'm missing a solution that has since been implemented?

Godiepi

Hi @LCash ,

It shouldn't be difficult to do what you are trying to do.

Make sure to select the correct Chart Type and also you might need to create a beastmode to use as a sorting option to make the 'Forecast' series be the first one so that it plots as a line and the rest of series get stacked in a bar.

LCash

@Godiepi thank you! That looks like what I need but I'm struggling to understand how you've implemented the solution. What's confusing me is what's generating the bar values as it all seems to be coming from a single table column?

If that's right it doesn't quite achieve what I want as the forecast is split out as a separate column in my dataset:

Does your solution rely on data which looks like the below?

If it does then I understand it and it definitely points to a solution at the processing level, but I was hoping to be able to track two metrics and split just one of them by a category.

Godiepi

@LCash Oh, I see what you are saying

so , with your dataset being formated as the first table in your post, you will need to

1) bring forecast column into the Y-axis (sum Aggregation)

2) for the 'Series' part you will have to add beastmodes for each Product doing the sum of metric like this below

Prodcut X: sum(case when `category` = 'x' then `metric` else 0 end) 
Product Y: sum(case when `category` = 'y' then `metric` else 0 end)  
Product Z: sum(case when `category` = 'z' then `metric` else 0 end)  

3) with the 3 beastmodes , drag and drop each on the Series, you can add as many as you want to the right side of each. unfortunately this is the only way I see it possible without a flow... I hope the number of products isn't a lot since you will need to create 1 per product

LCash

@Godiepi ok thank you for confirming - using multiple beastmodes is sadly what I was hoping to avoid!

jaeW_at_Onyx

@LCash i wouldn't recommend it, but you could put your product actuals into separate columns using PIVOT in Magic 2.0

it may seem counter intuitive but stacking the data (date, category, metric) would actually be the best solution , because if you had data that was not preaggregated (if you had multiple product x sales in one day) then you don't need your data to be compressed to a specific granularity as you do if your data is formatted (date ,category, actual,forecast)