group by , count by filed color , chart

hatra
edited February 11, 2022 in Analytics #1

Hi all,
I have a requirement where I need to apply highlight to present data by color ie =1 and <80 red, = 81 and 130 yellow, =131 and 500 green
and count the color based on grouping ( city) , then count the each color ie red 2, yellow 4, Green 6 so I can show each city on the chart (x) and count of each color on (y) separably? if I apply aggregation if would just total all, what is the best approach please

Comments

  • I attached an example that shows how to set the color based on the data value in a chart. I don't understand the question about grouping the city. If you choose your city column to be the X-axis category, the data will automatically be grouped by city.

    Warning No formatter is installed for the format ipb
  • thanks ,
    not sure how I created two discussion for same issue, I have another window with same discussion and sample where I attached sample report and data,
    (
    there are three columns that need to be presented on Y series on the chart, I can check that by creating multiple series but each column return 3 different values Low, Medium and high, so I want to count each of them and present total of low , total of Midium and total of High for each X series )

  • @jfranken
    Thanks , that was very helpful,
    how ever my requirement is a title more complected,
    I have 3 Y series and and I want to specify color for each, would you be able to provide me something for that please,

  • jfranken
    edited March 18, 2019 #5

    I haven't had a chance to look at your example, but I want to offer a quick suggestion. In beforeDrawDataPoint, dph.getSeriesValue() gets the name of the series being drawn and dph.getBaseValue() gets the name of the category. Add a "switch" statement or other conditional to set the fill based on one or both of those values. In the barColor.rptdesign example I posted earlier, beforeDrawDataPoint triggers 4 times (once for each bar on the chart). The dph.getSeriesValue() equals "Sales" each time, and the dph.getBaseValue() is "company A" the first time, "company B" the second, etc.

    Warning No formatter is installed for the format ipb
  • hatra
    edited March 19, 2019 #6

    Thanks for your reply Jeff,
    I am sure this is the solution and sound like a good suggestion, how ever I don't think I got the whole coding part,
    so the category is row["G_Country"] which has three bars for each city, and Value (y) Series has Series 1, Series 2,Series 3, and Series 1 should be Green, Series 2 should be Yellow, and Series 3 should be Red,
    so the code you had is:
    function beforeDrawDataPoint( dph, fill, icsc )
    {

    if( parseInt(dph) > 25 && parseInt(dph) < 35 )

    {
    fill.set( 242, 88, 106, 255 ); // change bar color to red
    }

    }
    I am not sure where to specify the Serise and category name and apply the switch,

    I appreciate in advance

    PS: where would you know the color fill.set code from ?

  • Hi Jeff thanks for your reply ,
    I believe your suggestion would work how ever I am a little limited on coding part of it,
    The category is ["G_City"] which present 3 bars for each cities,
    and Value (Y) Series are Series 1, Series 2, Series 3, so I would want to have Series 1 Green , Series 2 Yellow, Series 3 Red,
    not sure how to apply Series name, switch and colors for each bar based on your sample code from Barcolor report
    function beforeDrawDataPoint( dph, fill, icsc )
    {

    if( parseInt(dph) > 25 && parseInt(dph) < 35 )

    {
    fill.set( 242, 88, 106, 255 ); // change bar color to red
    }

    }

    any chance to get some help on the code ?
    how and where can we get the fill.set ( ****.****..x.x.x.x.x) // for the color for example what would be the fill.set () for purple ?

  • Regarding the colors, try searching the web for "rgba". There are many websites that help you get color codes. For example, https://hexcolortool.com/

    There are several formats for specifying colors. The fill.set() function takes values from 0 to 255.

    I am not clear on how you want to display the data. If you just want a count of row values in a given numeric range, you can add computed columns to your data set. For example, to get a count of the values in column "Number1" with a value between 80 and 130, the computed column expression would be something like:

    (row["Number1"] > 80 && row["Number1"] < 130) ? 1 : 0

    Then you can add a series in the chart that does a sum of the computed column. As I said, I'm not sure this is what you are trying to do, but I hope it is helpful.

    Warning No formatter is installed for the format ipb
  • Thanks for the information and respond
    I am trying to have my three side by side bar per the color I choose and I don't seem to be able to do that
    I have 3 Y series for each X series,
    on format chart screen am not able to do that , or i cant find it ( Series 1 Green , Series 2 Yellow, Series 3 Red)

    please attached

  • To set the bar colors to static (fixed) colors, Go to the "Format Chart" tab in the chart editor. Select "Series" on the left and click the "Series Palette" button (you might have to scroll down to see it). In the "Series Palette" editor, uncheck the "Auto" checkbox. Click the bars and select the colors. In the example below, your green bar will be changed to blue (top bar in editor is left bar on chart):

    If you want to change the bar colors dynamically based on the data value, use the code in the report that I posted earlier (barColor.rptdesign).

    Warning No formatter is installed for the format ipb
  • Thanks Jeff,
    but this option is only change the one of Value,
    from your screenshot above there are three under Series: Value (Y) Series -1 , Value (Y) Series -2 , Value (Y) Series -3
    I want each to have specific color
    thanks

  • In my screenshot, clicking the top bar and selecting a color will set the color for Value (Y) Series -1 (as shown). The second bar will set the color for Value (Y) Series -2. The third bar will set the color for Value (Y) Series -3.

    I apologize if I am misunderstanding the question again.

    Warning No formatter is installed for the format ipb
  • perfect , done
    thanks

  • Hey,
    just one last thing on this threat,
    can a bar chart with two series value be set to change color dynamically
    if series1 = 1
    "green"
    if else series1 = 2
    "yellow"
    end

    if series2 = 1
    "red"
    if else series2 = 2
    "black"
    end

    I have tried to use the other color formatting you have provided but it didnt work for me, probably i am doing something wrong with syntax
    thanks in advanced

  • I hope the attached example is helpful.

    Warning No formatter is installed for the format ipb
  • thanks again Jeff , I will give it go :)