Hi, I want to get the running Total in a Chart. How can I do this.WE have a Table with all registered Users of a Website and we want to show in a line CHart the Totals

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Running Totals on the Y-Axe and Date (Weeks) on the X

Photo of Joern

Joern

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
You have to use script and either (1) create a custom Chart or (2) modify an existing QuickBase Chart. 

Here is a quick example of modifying an existing chart using the Un-Highcharted Waters technique:

Un-Highcharted Waters Tasks Table ~ Click on CumulativeChart.js Button
https://haversineconsulting.quickbase.com/db/bk89redf6?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=529


In this example I simply (1)  create a bar chart and extract the options, (2) extract the y-axis values from the options, (3) calculate the cumulative values of the y-axes values, (4) insert the cumulative values back into the options, and (5) destroy and re-render the chart using all the original options along with the cumulative y-axes values. Back to watching cartoons.

If you need assistance implementing this or want an independent review of your cartoon watching behavior from an expert, fee free to contact me off-world using the information in my profile.
Photo of Joern

Joern

  • 0 Points
Thank you so much. How can I create a BUtton to do this?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
In regards to the Tasks table which contains the Button, here is the original explanation contained in my answer to another question:

https://quickbase-community.intuit.com/questions/1393471

I should add that this technique has emerged as the easiest way to create a button that when clicked executes JavaScript (rather than load a new page). While it is possible to modify other QuickBase pages that contains native buttons or inject your own button time has proven that it is more fuss than it is worth. Just create a new table called Tasks or Administration with the three fields [Script], [Button] and [Description] and launch all your scripts from this special table.

Eventually QuickBase will catch on that they should be supporting easier ways to run script rather than promoting the "no code" meme.
Photo of Joern

Joern

  • 0 Points
Dan, Thank you again. I never worked with Scripts in QB. I created a Task Table. But now i dont know how to implement the code and create the button etc.

Thank you
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
This is the button formula (text formula field with some HTML allowed):

"<a class=\"Vibrant Success\" onclick=\"$.getScript(gReqAppDBID+'?a=dbpage^pagename=" & [Script] &  "');return false;\">" & [Script] & "</a>"

As a test that you have the Tasks table setup correctly create a record with [Script] equal to "alert.js" and then create a user defined page named "alert.js". In the user defined page alert.js place the following code:

alert("you just ran the script in the file alert.js");

When you click the button you should get an alert, Once this is working you can use my script in this thread instead of alert.js.

I will try to answer your questions today but I am about to move from cartoon watching mode to election return watching mode and may not be able to answer further till tomorrow.
Photo of Joern

Joern

  • 0 Points
Thanks. The Button looks not like your button. It is only script and no button

But how can I create a user defined page?

Thanks
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
I guess QuickBase is calling user defined pages code pages now - that is progress!

Home | Settings | Pages | New Page | Code Page

The button should look exact like mine if you used the formula I listed. Short of that post your formula or a screenshot.
Photo of Joern

Joern

  • 0 Points
perfect. I got it. It works. Thank you. One additional question. Is this also possible for charts with multiple lines (in line charts). I have 5 lines in my charts (total registrations, total registrations without login...etc.) Is it possible to get from every line the total? I used your solution for my chart with multiple lines but the script only modify one line... Thanks
Photo of Joern

Joern

  • 0 Points
is this possible? that would be very nice
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
You can just repeat the code block for each series. Here is the code for the second series (note the series[1] indexing):

        injectedScript += "var sum = 0;";
        injectedScript += "options.series[1].data.forEach(function(item, index) {";
        injectedScript += "sum += item.y;";
        injectedScript += "item.y = sum;";
        injectedScript += "});";

I should mention that this is just quickly written code to act as a proof of concept. I probably would write the code differently for a production project.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
FWIW, it is worth noting that the options object fully defines all the parameters of a HighChart. Every feature of the chart (chart type, chart data, axes, legends animation etc) is represented by some property in the options object. Here is a visualization of the unmodified chart options for the report (https://haversineconsulting.quickbase.com/db/bk84gnxdr?a=q&qid=5) in question:

http://www.jsoneditoronline.org/?id=7f672323b8e31d16f9afb7af69aec3ea