Breakdown chart by city with filters

Hello !

I currently use a graph allowing me to know where my customers are located.

For the Chassieu city, I only have one client and it is not sufficiently representative to display it in the graph. I would therefore like to remove it from the display of the graph.

For the display of this graph, I have a filter that I must not remove:

let filterStart := 'Début du filtre';
let filterEnd := 'Fin du filtre';
(select Comptes)[date('Première interaction') >= filterStart and
    date('Première interaction') <= filterEnd]

This filter allows me to filter customers between 2 dates and therefore to see changes over time.

For this graph, I did not create a specific table. I just have a city field on the Accounts table that I display as a column to get the title then a second time to get the #total.

With this method, I can't tell the graph that I want to display only cities that have more than 5 Accounts.

Can you help me ?

2 replies

    • Fred
    • 2 mths ago
    • Reported - view

    You can try this in a new formula field to see if the correct records are returned:

    let filterStart := 'Début du filtre';
    let filterEnd := 'Fin du filtre';
    let x := (select Comptes)[date('Première interaction') >= filterStart and
        date('Première interaction') <= filterEnd];
    let z := unique(x.cityName);
    let finalCities := z[var v := this;
            count(x[cityName = v]) > 1];
    concat(x[var v := cityName;
            count(finalCities[= v]) > 0])

    You will need to change the 'cityName' on lines 5, 7 and 8 to match your field name. Otherwise the rest should work.

    Here is my thinking:

    Line 4: take your select and put it in a variable, "x" which we will use later

    Line 5: make a unique list of city names from x and put that in a variable, "z"

    Lines 6 - 9: make a list of city names that appear more than once in the variable x and puts it in variable "finalCities"

    Lines 10 - 11: display a list of records that match the city names that are from finalCities.

    If this works then you can copy it to your graph and remove the concat() command and you will get an array of record Id.

      • Créateur de bien-être
      • Sebastien_Guillet
      • 2 mths ago
      • Reported - view

      Fred This formula works perfectly! I will be able to draw inspiration from this logic for other formulas. Thank you so much !

Content aside

  • Status Answered
  • 2 mths agoLast active
  • 2Replies
  • 39Views
  • 2 Following