Making a Dashboard 'What if?' Calculator in Chartio


Looking for ways to build out a truly interactive dashboard? Try building a Dashboard Calculator for those ad hoc ‘What if?’ types of analyses. That way users who consume data only from dashboards can get valuable insights on what could happen in the future based on their inputs.

As an example, let’s create a dashboard calculator which uses dashboard variables / controls to calculate the insurance tier and price for someone considering insurance plans. That way someone chatting with a potential customer can quickly investigate different options to find the right plan for them.

Here’s what our end product might look like:

In the above example, each Single Value Indicator chart in the middle has advanced weighting and math built into the Data Pipeline using CASE statements which reference the dashboard variables.

To accomplish this, first you’ll want to build dashboard variables onto your dashboard by using the Add Control button from the right hand tool bar. Make sure to name them appropriately so you’ll know what each one of them is intended to control. For example, I’m naming my Text Input intended for inputting the insured’s age {AGE}.

Now I’ll create a Single Value Indicator Chart where I drag over my Rate column and hit the Run Query button. Now in Chartio’s Data Pipeline, I can build CASE statements into Edit Column steps which will apply the appropriate math:

Here’s an example of an entire CASE statement written into one of the steps:

CASE WHEN {SMOKER} = ‘Yes’ THEN 2*(“Rate”) WHEN ‘Not within last 10 years’ THEN 1.5*(“Rate”) ELSE “Rate” END

Remember that everything in Chartio’s Data Pipeline uses SQL Lite syntax.

Because the math is built into each Single Value Indicator chart, the math applied is uniform, ensuring that everybody using this dashboard is operating within a single source of truth while giving them the ability to do ad hoc / ‘What if?’ analyses.