VL Web Google Line Chart - (RESOLVED)

[jyoung]

I am trying to get a widget to display some summary data.
What I'm struggling with getting my data shaped in a way that can be presented in a Google Line Chart.

I have data that looks like this, where each month is a calculated value (all in a List definition).

Code: Select all

def_list name(#Trendlines) fields(#wk_String1 #wk_Signed1 #wk_Signed2 #wk_Signed3 #wk_Signed4 #wk_Signed5 #wk_Signed6 #wk_Signed7 #wk_Signed8 #wk_Signed9 #wk_Signed10 #wk_Signed11 #wk_Signed12) type(*WORKING) entrys(*MAX)

The data itself looks like this

Code: Select all

BC			JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEPT	OCT	NOV	DEC
Office Services		2290.26	1804.01	1256.54	1256.54	1256.54	1256.54	1256.54	1256.54	1256.54	0	0	0
Light Industrial	1215.83	1300.69	1846.34	1279.5	2215.23	3266.56	0	2545.42	626.14	0	0	0
Skilled  Trades		1215.83	1300.69	1846.34	1279.5	2215.23	2074.81	1686.46	2545.42	626.14	0	0	0
Professional Services	1215.83	1300.69	79.21	1279.5	2215.23	2074.81	1686.46	2545.42	626.14	0	0	0

I want to get this displayed in a line chart that looks like this

Capture.PNG (20.97 KiB) Viewed 4427 times

Where instead of toko, new york, berlin, london the BC values (Office Services, Light Industrial etc.) are used.

The problem I am running into is that Google Charts wants the Months to be "Vertical" instead of "Horizontal" as per this example

Code: Select all

          var data = new google.visualization.DataTable();
            data.addColumn('string', 'Month');
            data.addColumn('number', 'Tokyo');
            data.addColumn('number', 'New York');
            data.addColumn('number', 'Berlin');
            data.addColumn('number', 'London');
            data.addRows([
               ['Jan',  7.0, -0.2, -0.9, 3.9],
               ['Feb',  6.9, 0.8, 0.6, 4.2],
               ['Mar',  9.5,  5.7, 3.5, 5.7],
               ['Apr',  14.5, 11.3, 8.4, 8.5],
               ['May',  18.2, 17.0, 13.5, 11.9],
               ['Jun',  21.5, 22.0, 17.0, 15.2],
               
               ['Jul',  25.2, 24.8, 18.6, 17.0],
               ['Aug',  26.5, 24.1, 17.9, 16.6],
               ['Sep',  23.3, 20.1, 14.3, 14.2],
               ['Oct',  18.3, 14.1, 9.0, 10.3],
               ['Nov',  13.9,  8.6, 3.9, 6.6],
               ['Dec',  9.6,  2.5,  1.0, 4.8]
            ]);

This is causing me all kinds of headaches, but the Business Class (BC in the above data) is variable, not the months.

It seems like Google is wanting me to "pivot" this data so that the Months are horizontal and the Business Class is vertical, BUT I have an unknown number of Business Classes. Right now there is just 4, but that can easily expand to N+.

I feel like I'm missing something stupid.

Any thoughts on how I can get this to work?

Thanks,
Joe

[jyoung]

Good grief, this caused me all kinds of headaches.

I really, really don't like Javascript, but sometimes its the only way to get stuff done.

The main problem is that I need to transpose the list, basically make the top row columns and the first column rows.
I could not figure out a way to do this in VL, but I did find a way to do it in javascript thanks to Hobs at StackOverflow.

If anyone knows how to do something like this in VL, that could be a useful trick to know, so please share.

Using this transpose function, it becomes a simple matter to transpose the data that gets added to the widget and then add each transposed element to the chart.

Joe