Configure dimension reference line for bar chart with multiple dimensions
Hi all, I have a multiple dimensions bar chart with Year as Group, Week as Bars and total sales as Measure. I need to have some dimension reference lines on specific year and week, for example, Week 5 of 2024, Week 1 of 2023 etc. It is pretty straightforward for chart with single dimension as I can just specify the dimension value in the expression but not for chart with multiple dimensions. Does anyone has any experience on how can this be achieved? Any help is greatly appreciated.
Hi, configuring reference lines for a bar chart with multiple dimensions like Year and Week can be a bit tricky, but it’s achievable by creating conditional reference lines based on a combination of your dimensions.
This is what you can do
You can create a calculated dimension to combine Year and Week so that you can reference specific combinations like Week 5 of 2024 or Week 1 of 2023.
Year & '-' & Week
This creates a combined value like 2024-5 or 2023-1. Use this as your grouping or an additional dimension.
Go to the Properties panel of your bar chart, and under the Add-ons section, enable Reference Lines.
You can then use a conditional expression to add reference lines based on your combined dimension.
Example for Week 5 of 2024: If(Year = 2024 and Week = 5, Sum(Sales)) Or, if you’re using the calculated dimension created earlier:
If(Year & '-' & Week = '2024-5', Sum(Sales))
for each specific year-week combination (like Week 1 of 2023), add additional conditions:
If((Year = 2024 and Week = 5) or (Year = 2023 and Week = 1), Sum(Sales)) This will ensure that reference lines appear for multiple specific points.
You can style the lines (e.g., change color or width) for easier visibility, and you may also want to label them for clarity (e.g., "2024-Week 5 Reference").
Once your reference lines are set, test the bar chart to make sure the lines align with the correct Year and Week. Adjust the expressions if you need to handle more complex combinations.
Hi @diegozecchini, thanks for the suggestion! It is indeed a workable solution. However, I really would like to have the Week and Year separated as it looks cleaner in the chart. Guess I'll have to settle with this for now until Qlik manages to release more chart features.
Hi @poklegoguy , as promised I tought a bit on it.
Here are a couple of alternatives you can try:
1) Use Dual Axis for reference lines If you want to keep Year and Week separate but still have reference lines, you can explore using a dual-axis chart. Here, you can keep Year and Week as individual dimensions and then plot your reference lines on a secondary axis using conditional expressions for the specific combinations of Year and Week, similar to what I suggested earlier. This way, the chart looks cleaner without merging the dimensions, but you still get the reference points you need.
2)Invisible Calculated Dimension You could maintain your original dimensions of Year and Week for the bars while adding an invisible calculated dimension (Year & '-' & Week) specifically for the reference lines. Here’s how: -Use your calculated dimension (e.g., Year & '-' & Week) for your reference lines but keep it invisible in the chart. -Keep the Year and Week displayed separately in the axis labels. This keeps your reference lines working without altering the visible structure of your chart.
