Web > Charts > Chart Type Walkthroughs > Cartesian

Bump Chart of Desk Rankings¶

Known issues

Some chart features have known issues in the current release. See Known issues.

This walkthrough draws each desk's rank week by week, answering "Who overtook whom?"

Prerequisites¶

Editor mode: click once on the screen, then press Ctrl+d. The AMIDB Shell Tool and the gallery are used as in the quickstart.
Data: the example data that stage 1 creates. To use a table of your own instead, see With your own data.

1. Create the example data¶

The chart reads a table in the Center, so create the table first.

Select Dashboard -> AMIDB Shell Tool....

Run:

CREATE PUBLIC TABLE ChartDemoRanks (Week String, WeekNo Long, Desk String, Rank Long);
INSERT INTO ChartDemoRanks VALUES ("Wk 40", 40, "Equities", 1), ("Wk 40", 40, "Rates", 2), ("Wk 40", 40, "FX", 3),
 ("Wk 41", 41, "Equities", 2), ("Wk 41", 41, "Rates", 1), ("Wk 41", 41, "FX", 3),
 ("Wk 42", 42, "Equities", 3), ("Wk 42", 42, "Rates", 1), ("Wk 42", 42, "FX", 2),
 ("Wk 43", 43, "Equities", 2), ("Wk 43", 43, "Rates", 3), ("Wk 43", 43, "FX", 1);
SELECT * FROM ChartDemoRanks;

Check that the lower pane shows the 12 rows.

2. Create the chart¶

The gallery creates a datamodel that reads the table, then places the chart.

Select Windows -> New Window, then the edit button on Blank1, then "Create Table / Visualization / Form".
Select "AMI (__AMI)", then "Next >>".
Select the "USER" tile ChartDemoRanks, then "Next >>".
Select "Test (alt+enter)", then "Next >>".
Select the "Cartesian" tab, then the "Bump" tile.
Set the fields below the tiles:

Field Value

"Period label:" Week

"Period:" WeekNo

"Series:" Desk

"Rank:" Rank
Select "Finish".
Check that the window shows 3 lines across "Wk 40" to "Wk 43", each ending in its desk's name, with rank 1 at the top: FX there in "Wk 43".

Result¶

3 lines, one per desk, crossing as the ranks change: Equities falls from 1 to 3 and back to 2; FX rises from 3 to 1.

The chart's own PNG export, from AmiScript toImagePng; see Chart type walkthroughs.

To check without looking, read the render stats: session.getDevTools().getAllRenderStats() in the AMIDB Shell Tool, with "Run on this web session" selected (see Running a script), or web_debug op=panelRenderStats over MCP. The "Ranks" layer reports Rows 12, "3 groups", and an empty Problem.

With your own data¶

If your table holds a score for each series in each period rather than a rank, shape the rows in the datamodel's query. To try it on example rows, run this in the AMIDB Shell Tool first:

CREATE PUBLIC TABLE ChartDemoDeskPnl (Week String, WeekNo Long, Desk String, Pnl Double);
INSERT INTO ChartDemoDeskPnl VALUES ("Wk 40", 40, "Equities", 30), ("Wk 40", 40, "Rates", 20), ("Wk 40", 40, "FX", 10),
 ("Wk 41", 41, "Equities", 15), ("Wk 41", 41, "Rates", 25), ("Wk 41", 41, "FX", 5),
 ("Wk 42", 42, "Equities", 8), ("Wk 42", 42, "Rates", 40), ("Wk 42", 42, "FX", 12),
 ("Wk 43", 43, "Equities", 18), ("Wk 43", 43, "Rates", 9), ("Wk 43", 43, "FX", 22);

Remove it afterwards with DROP TABLE ChartDemoDeskPnl;.

Follow stage 2 with the "USER" tile ChartDemoDeskPnl, or the one for your own table, in place of ChartDemoRanks.

When the gallery shows the datamodel's query, replace the query with:

{
  CREATE TABLE Raw AS EXECUTE SELECT * FROM ChartDemoDeskPnl WHERE ${WHERE};
  CREATE TABLE ChartDemoRanks AS PREPARE Week, WeekNo, Desk, rank(-Pnl) AS Rank FROM Raw ORDER BY WeekNo PARTITION BY WeekNo;
  DROP TABLE Raw;
}

Select "Test (alt+enter)".
Check that the "Tables" tab shows ChartDemoRanks alone, with the same 12 ranks as the example table in stage 1.

Then continue with stage 2: the fields are the same, and any later stages apply as written. To do this over MCP, put the same statements in the onProcess of the datamodel that step 1 of "Over MCP" imports, with USE ds="AMI" before each EXECUTE.

rank gives the lowest value 1, so ranking -Pnl gives the highest P&L rank 1. PARTITION BY WeekNo ranks each week on its own.

Over MCP¶

An agent builds the same chart from the same table. Connecting an agent is covered in the AI section; the tools are on Building charts over MCP.

Create the datamodel with web_execute op=importDatamodel and this dmJson, then run it once with op=initDatamodelSchema and dmName ChartDemoRanks:

{"id": "ChartDemoRanks", "queryMode": "startup", "callbacks": [{"name": "onProcess", "amiscript": ["{ CREATE TABLE ChartDemoRanks AS USE ds=\"AMI\" EXECUTE SELECT * FROM ChartDemoRanks; }"]}]}

Create the chart:

{"windowName": "ChartDemoBump", "templateKey": "tplBump", "datamodelId": "ChartDemoRanks", "dmTable": "ChartDemoRanks", "title": "ChartDemoBump", "bindings": "{\"CAT\": \"Week\", \"XAX\": \"WeekNo\", \"SER\": \"Desk\", \"RANK\": \"Rank\"}"}

Run the datamodel again with web_execute op=executeDatamodel, dmName ChartDemoRanks. A chart created over MCP can stay empty until its datamodel runs again, with its axes at 0-100.
Confirm that every layer reports the rows above and an empty Problem: run web_debug op=panelRenderStats.

The call is web_create_chart_from_template. Over MCP the rank axis starts the other way, with rank 1 at the bottom. To put it on top, export the chart with web_console op=exportPanel, add "flip": true to the left axis, pass the result to web_execute op=updatePanel, and run the datamodel again with op=executeDatamodel.

Removing the example¶

Remove the chart, then its datamodel, then the table, so that the next walkthrough can reuse the names:

Close the chart's window with its close button, and select "Delete" when asked whether to delete the panel.
Select Dashboard -> Data Modeler..., expand "Datamodels", right-click the chart's datamodel, ChartDemoRanks, and select "Delete Datamodel", then "Yes". If that name was taken when stage 2 ran, the gallery added a number to it, as in ChartDemoRanks1.
Run DROP TABLE ChartDemoRanks; in the AMIDB Shell Tool.
Check that SELECT * FROM ChartDemoRanks; reports Unknown table: ChartDemoRanks.

Over MCP, delete the chart with web_danger op=deletePanel, then the datamodel with op=deleteDatamodel, dmName ChartDemoRanks, and drop the table as in step 3. deleteDatamodel refuses a datamodel that has been committed; delete that one in the Data Modeler as in step 2. See Removing an agent's chart.

Field	Value
"Period label:"	`Week`
"Period:"	`WeekNo`
"Series:"	`Desk`
"Rank:"	`Rank`