Combine bar and line with different dimensions

[#1]

Hi,

Is it possible to show bars and lines in same chart, so that bars would have month dimension and line would have day or hour dimension? Ie. the other fact has higher resolution.

[Anonymous]

It is possible to have something like this.  Don't expect it to look as nice as on your chart - the bars are monthly totals, so they'll be much taller than the line.  And located at the beginning of the months.

This is how to do it:

Create a combo chart with one dimension - Date.  On Axes tab, check "Continuous".  On Number tab, use Date with format pattern MMM-YY.

Two expressions:

1 - Monthly Sales, displayed as bars, expression aggr(sum(Sales), Month).  On Presentation tab, uncheck "allow thin bars".  They still will be thin but not too thin.

2 - Daily Sales as line, expression sum(Sales)

Try this and maybe you can improve from there...

Regards,

Michael

[#3]

I'm investigating another possibility, where I divide the monthly sum to each day of the month. This way I would have two lines, other having the same value for each day and other having unique value for each day.

But I can't figure out how to do this dividing.

[Anonymous · Accepted solution]

It is a great idea to use two lines in the chart.
The first expression will be a little more complex:

aggr(nodistinct sum(Sales),Month) / aggr(nodistinct count(distinct Date)Month)

For a better presentation of this expression, try display option for line "Plateau (center)", it will be almost like a bar for the whole month.

You probably don't need to use "continuous" on axes - just see what looks better.

Regards,
Michael

[#5]

Thanks, this gives me a nice result