Charting 1 Value Vs Another?

[codemonkey]

Hello

 

I'm new to Caspio but I am needing to make a report or chart, preferably a chart, from some spreadsheet data.

 

The spreadsheet I've got is broken into months (by a date column), with each month having a list of enquiries. Those lists are then separated into won enquiries and not won enquiries within each month (done by color coding on the sheet).  Each enquiriy has it's own value (for examples sake lets say $100).

 

I've imported the sheet, I've replaced the color coding with a separate field which has true/false in it, true being enquiry won.  The dates are all date formatted, and the value of each enquiry is a currency field.

 

What I need from that data is this -

 

Total number of enquiries with the value of all enquiries (in $) vs number of enquiries won with the total value of those enquiries (in $).  I need this shown on a month by month basis over a year.

 

So for example I get 6 enquiries each month adding up to a value of $600, I only win 3 of those enquiries each, adding up to a value of $300.

 

So the chart I'm looking for, lets say it's a bar chart, would show me 2 columns per month, total enquiries and won enquiries, and both bars would have a value in $

 

I thought a pie chart would work for this but I can't get it to work and so far all I can achieve is a 2d vertical bar chart which shows either the won cases or the total cases and their total value per month.

 

Ideally I'd also like the end user to be able to search for each year, so they'd input 2010 for example and they'd get the above chart for that year, and this needs to be a live chart so if the user is inputting data for a year (via a submission/search form I've already made) and then goes to look at the chart for that year it reflects the new/updated data.

 

Can anyone help me on this?

 

TIA

 

[Jan]

Hello TIA,

 

Welcome to Caspio forum!

 

If I understand correctly, you can follow the following steps:

1. Create a Chart - 2D Column DataPage.
2. In the "Select Search Fields" screen, select the date and the "win" fields.
3. In the "Configure Search Fields" screen,
   • In the date field, select the "Dropdown" Form Element, select the "Year" Precision, and enter years that are used in your table (you can also use a look up table);
   • In the "win" field, select the "Hidden" Form Element, check the "On load, receive: Yes" checkbox.
4. In the "Configure Chart Options" screen,
   • In the "Category" field, select the "date" Category field, select the "Date rollup" Group date by, select "Month" Date rollup;
   • In the Seria1, select "sum" Data series field and COUNT Aggregation;
   • Add the Seria2, select "sum" Data series field and SUM Aggregation.

Does it works for you?