Une boîte à outils pour construire des graphiques élémentaires sur la WebApp en utilisant SQL. Pour des raisons de confidentialité, les données ont été anonymisées.

  • Carte de pointage

  • Diagramme à jauges

  • Diagramme à barres horizontales et diagramme à barres

  • Diagramme circulaire

  • Graphique en ligne

  • Diagramme à barres empilées

  • Tableau

  • Carrousel 

Carte de pointage

Affiche une valeur pour montrer un niveau global de performance. Exemple de cas d'utilisation : Ventes totales, Nombre de visites, Nombre de représentants actifs...

 

# 1-dimension chart : the query needs to return 1 value

SELECT
ROUND(SUM(total_product_value),0) as x
FROM
  w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)
CODE

 

 

Gauge chart

Affiche le fonctionnement d'un niveau de performance réel par rapport au niveau budgété.

La valeur peut être soit un simple nombre, soit un pourcentage. Vous pouvez définir une cible en dehors de la requête SQL.

Exemple de cas d'utilisation : Couverture des points de vente, utilisateurs actifs vs utilisateurs enregistrés...

 

 

# 1-dimension chart : the query needs to return 1 value

# Gauge - example with a random figure

SELECT
nb_outlets_visited
FROM
  w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

# Gauge - example with a percentage

# Using Common Table Expressions, create a first table to count the number of active users 
# Assign it the name table1

WITH table1 AS (
  SELECT
    COUNT(DISTINCT user_id) as active_users
  FROM
    log
LEFT JOIN mobile_users ON mobile_users.id=log.user_id 
  WHERE
    user_type = 'MOBILE'
    AND action_code = 'CHECK_IN'
    AND system_action IS NOT true
 AND $date_filter:bi_timestamp$
AND (email IS NULL OR email NOT LIKE '%optimetriks%')
    AND user_id IN (
      SELECT
        user_id
      FROM
        teams_mobile_users
      WHERE
        $teams_filter:team_id$
    )
),

# Create a second table to count the number of registered users
# Assign it the name table2

table2 AS (
  SELECT
    COUNT(DISTINCT user_id) as registered_users
  FROM
    teams_mobile_users
LEFT JOIN mobile_users ON mobile_users.id=teams_mobile_users.user_id 
  WHERE
    $teams_filter:team_id$
AND licensed IS true
AND (email IS NULL OR email NOT LIKE '%optimetriks%')
)

# Compute the percentage of active users among the registered users

SELECT

CASE WHEN registered_users > 0 
THEN 100 * active_users / registered_users ELSE 0 END

from
  table1,
  table2
CODE

Diagramme à barres horizontales et diagramme à barres

Recherchez une valeur spécifique dans différentes catégories.
Exemple de cas d'utilisation : Nombre de commandes par marque, ventes par produit...

 

# 2-dimensions chart : the query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.

# X values can be either text or date.
# Always make sure you have the correct format selected.

SELECT

name as x,
nb_items as y

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

GROUP BY 1
ORDER BY 2 DESC
CODE

Graphique circulaire

Affiche les relations entre les parties et le tout.
Exemple de cas d'utilisation : % de présence d'un élément...

 

# 2-dimensions chart : the query needs to return 2 columns 
  named value (here: 31, 66) and label (here: Visible, Non visible)
  No other column name will be accepted.

SELECT

nb_items as value,
name as label

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)


CODE

 

Graphique linéaire

Met l'accent sur la forme générale d'une série de valeurs dans le temps.
Exemple de cas d'utilisation : Point de vente visité dans le temps, Ventes dans le temps....

 

# 2-dimensions chart : the query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.

# X values can only be date

SELECT

_completed_at as x,
nb_items as y

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

GROUP BY 1
ORDER BY 2 DESC
CODE

 

Diagramme à barres empilées

Recherchez une valeur spécifique dans différentes catégories.  
Exemple de cas d'utilisation : Ventes totales, Nombre de visites, Nombre de représentants commerciaux actifs...

 

METHOD 1 : using the sub-queries

# CREATE THREE DISTINCT SUBQUERIES FOR THE THREE DISTINCT CATEGORIES
# each query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.



# SUBQUERY 1 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 
 
SELECT
_brand as x,
COUNT(DISTINCT CASE WHEN past_sales_sku='Il y a moins de 6 mois' THEN _place_id END) as y
FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand

# SUBQUERY 2 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 

SELECT
_brand as x, 
COUNT(DISTINCT CASE WHEN past_sales_sku='Il y a plus de 6 mois' THEN _place_id END) as y

FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand



# SUBQUERY 3 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 

SELECT
_brand as x,
COUNT(DISTINCT CASE WHEN past_sales_sku='Jamais' THEN _place_id END) as y
FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand



METHOD 2 : using the row query and the main query
# ROW QUERY
# the row query needs to return 2 columns 
  named label (value displaying on the dashboard) and 
  value (value stored in the $row_query_result$)
  No other column name will be accepted.
# The main query needs to return 2 columns
named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.
The link between the main query and the row query is done in  the WHERE statement 
by referring to the row query result with the expression $row_query_result$
  
For example to get the activity in the log per user

# ROW QUERY

SELECT
  CONCAT(first_name, ' ', last_name) as label,
  id as value
from
  mobile_users
LEFT JOIN (SELECT COUNT(id) as activity, user_id FROM log_all 
WHERE $date_filter:timestamp$ GROUP BY user_id) t ON mobile_users.id=t.user_id
WHERE activity > 0


# MAIN QUERY

SELECT
  count(DISTINCT id) as y,
action_code as x
FROM
  log_all
WHERE
  user_id IN ($row_query_result$)
  AND $date_filter:timestamp$
AND (action_code='CREATE_SUBMISSION' OR action_code='RUN_DASHBOARD')
GROUP BY x


CODE

 

Tableau

Affiche une grille qui contient des données liées dans une série logique de lignes et de colonnes.
Exemple de cas d'utilisation : Ventes totales, Nombre de visites, Nombre de représentants commerciaux actifs...

 

# Multiple dimensions chart: it can returns many columns.
  The column name will display as header in the table

# Create "table1" to select all the fields to display in the table

WITH table1 AS (
  SELECT
    _user_id,
    _user_name,
    _place_id,
    _id,
    total_product_value,
    CASE
      WHEN sale = 'Oui' THEN 1
      ELSE 0
    END AS success,
    _completed_at
  FROM
    w_id
  WHERE
    $date_filter:_completed_at$
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)
)

# Compute the required indicators for the table and group by the "active user" field (for this case) 

SELECT
  DISTINCT _user_name as active_users,
  COUNT(DISTINCT _place_id) as "clients_visited",
  COUNT(_id) as visits,
  SUM(success) as "# Sales",
  100 * SUM(success) / COUNT (_id) as "%Conversion",
  ROUND(SUM(total_product_value), 2) as sales
FROM
  table1
GROUP BY
  _user_name;

CODE

Carrousel

Affiche une bibliothèque d'images avec une étiquette pour chaque image

 

# 1 dimension chart
  The query must return 1 column named url and containing the url for the pictures
  

SELECT
url
FROM uploads
WHERE $date_filter:created_at$
CODE