How to create this sort of chart? (sample QV and picture provided)

[#1]

We are running a sea port. We look for a chart to visualize, the occupation of the different docks (Aa,Bb,Cc). If there is a ship (Aa=1) there is a colored point, else (Aa=0) there isn't. The data comes in discrete 1 minute intervalls.

We would like to plot these different docks on a diagram as follows:

I've tried the Grid Chart, Scatter Chart and Line Chart, but none of my tries resulted in the desired result. Do you have an idea, how to do this?

What should I take as dimensions and what should be the expression?

An additional feature would be the posibility to aggregate by hour/day/week, and show the amount of time as bubble size.

In other words, it's the visualisation of a table like this (here aggregated by hour):

Hour Aa    Bb      Cc

01      0       52      10

02      23     25      60

03      25     10      33

04      0       60      36

05      12     60      60

06      60     15      23

07      5       0        0   

08      0       0        0

...     ...       ...      ...

I also attached a sample file.

Best,
Jonas

[#2]

Check the QV file attached above.

[erichshiino]

Hi, Jonas.

If you will always have only A, B, C, you can use a line chart with some expressions.

I created one for each line and multiplied it by different numbers creating an offset.

Let me know if you need a more generic solution.

Regards,

Erich

[#4]

Like this?

[#5]

Hej federico!

This looks really cool! Looks as if you used some different data than I had (according to the plot: Port Aa has no occurence during the first hour. The bubble sizes seem to be all similar.) The CrossTable idea is very new to me. I will definitely look into that now.

I think I would need your file PruebaScatter.xlsx. Can you send this too, or make it inline?

Best,

Jonas

[#6]

Hej Erich! This looks awsome, thank you! The idea with the multiplication by [1,2,3] is really cool, thank you! 😃

Best,

Jonas

[#7]

Here is the xlsx file i used.

The idea of a crosstable is to have the port as a dimension disregarding is horizontal in the excel file.