10 Replies Latest reply on Nov 22, 2013 3:39 AM by Bethany Lyons

    color a chart with aggregated field

    Paul-Benoît Perche

      Hi evreybody,

       

      I have a report with a bar chart.

      It is based on one dimension (a kind of "time" field) and a measure which is taken as an average. And the bars are colored based on a threshold. i.e. if the measure is above the threshold, one colour, and another colour if below, as illustrated below:

      graphe1.tiff

      Now, I want to "aggregate" the time dimension by using the following formula:

       

      DATETIME((INT([Increment]/[Nb sample])*[Nb sample])/48/3600/24)

       

      where

           [Increment] is the initial ordered field that I have to convert to a time dimension (values 1, 2, 3, 4, etc.)

           48 because there is 48 "increments" in one second in the initial file

           [Nb sample] is the number of sample that I want to aggregate in one (for example: 3 if I want to display one measure every 3 samples.

       

      That works fine. The measure is averaged

      But the issue is that the color of the bar shows the initial details i.e. eventually several colours for one bar as illustrated below. And I can't find out how to avoid this and force to color the bar according to the average value.

       

      graphe2.tiff

      It would be great if someone could help me with this perhaps basic issue.

      Thanks in advance.

       

      PB

       

      Ce message a été modifié par : Paul-Benoît Perche Please find attached, the related packaged workbook for better analyze ;) PB

        • 1. Re: color a chart with aggregated field
          Ramon Martinez

          Hi Paul,

           

          It seems that .tiff format is not allowed to upload images here. We are not able to see your images.

           

          It would be nice if you post you package workbook, which is easier for any in the community to help you.

           

          Best,

          Ramon

          • 2. Re: color a chart with aggregated field
            Paul-Benoît Perche

            Hi Ramon,

             

            Thanks for your reply. I have just modified the message with a package workbook.

             

            Best

            PB

            • 3. Re: Re: color a chart with aggregated field
              Ramon Martinez

              Hi Paul,

               

              See results of the attached workbook and let me know if that reach your requirements.

               

              best,

              Ramon

              • 4. Re: Re: Re: color a chart with aggregated field
                Paul-Benoît Perche

                Hi Ramon,

                 

                Thanks a lot.

                This work fine with the bar chart, although I am sure I tried that before... Probably something else was wrong . And I don't know why, I had to do it twice in my workbook in order to reproduce what you did... Anyway, that works perfectly.

                 

                And I tried to do the same thing with a "Zone" chart. But again I can't do it. See the two zone sheets in the attached package workbook.

                And I can't understand why.

                Could you explain me why?

                 

                Thanks a lot in advance

                 

                Kind regards

                PB

                • 5. Re: color a chart with aggregated field
                  Ramon Martinez

                  Hi Paul,

                   

                  It seems that calculated field Colour avg works OK for bar Chart with formula:

                  if (avg([Measure1])> [threshold] or avg([Measure1])< (-1*[threshold])) then "Above" else "Below" END

                   

                  but it does not in Area Chart

                   

                  In Area Chart, calculated field Colour works Ok with formula:

                  if ([Measure1]> [threshold] or [Measure1]< (-1*[threshold])) then "Above" else "Below" END

                   

                  Interesting!! I don't know why.

                   

                  Best,

                  Ramon

                  • 6. Re: color a chart with aggregated field
                    Paul-Benoît Perche

                    Hi Ramon,

                     

                    In the Area chart, in my workbook, the calculated field with the formula:


                    if ([Measure1]> [threshold] or [Measure1]< (-1*[threshold])) then "Above" else "Below" END


                    works fine when the parameter "Nb sample" is equal to 1. But if I increase the value of this parameter, I have again several colors for the same time, as the initial issue with the bar charts. Did you do something else in your workbook?


                    Thanks a lot.


                    Kind regards

                    PB

                    • 7. Re: Re: color a chart with aggregated field
                      Ramon Martinez

                      Hi Paul,

                       

                      In your workbook, worksheet zone is working ok, with the only problem that there are overlaps in some time period. That is because of Area chart.

                       

                      I think that it is better to use bar chart using Colour avg to classify the tones. Decrease the Size of bars in the worksheet bar chart to avoid overlapping of bars.

                       

                      I'm posting the workbook TEST3 where worksheets: bar chart and zone are working properly. Again, I prefer the visual of worksheet bar char.

                       

                      best,

                      Ramon

                      • 8. Re: color a chart with aggregated field
                        Paul-Benoît Perche

                        Hi Ramon,

                         

                        Thank you very much for your help.

                        In your workbook, worksheet zone is working ok, with the only problem that there are overlaps in some time period. That is because of Area chart.

                        Then, we can't actually say that it works When I increase the parameter "Nb sample", there are overlaps everywhere...

                        Would it be possibly a bug of area charts? Or have I missed something?

                         

                        I think that it is better to use bar chart using Colour avg to classify the tones. Decrease the Size of bars in the worksheet bar chart to avoid overlapping of bars.

                        Yes, Bar charts are good, but the thing now is that it would be nice if the size of bars could be adapted automatically when the parameters are changed. But I could not find a way to do it. That's why I tried the Area charts.

                        Would you have an idea on this?

                         

                        Kind regards

                         

                        PB

                        • 9. Re: color a chart with aggregated field
                          Ramon Martinez

                          Hi Paul,

                           

                          I played more time with your workbook and It seems Area Chart is not a good visual for your purpose as when Nb sample is increased to a big value -let's say 59- two areas orange and blue stack one over the other. Definitively, this is not an option.

                           

                          It is different in the case of Bar Chart. The inconvenient is the width (size) of the bar with Nb sample=1 overlap neighbor bars. The solution is to set Size to a small value. I was trying to find a way to control the bar size  based on the value of Nb Sample but with no success.

                           

                          Best,

                          Ramon

                          • 10. Re: color a chart with aggregated field
                            Bethany Lyons

                            The reason you are seeing these differences is because in the row level calculation, colour is a dimension. Therefore it acts as a table partition and is sent to the 'group by' clause of the sql statement. This becomes more obvious if you change the chart to a line:

                            line1.PNG.png

                             

                            Since the average colour field is an aggregation, it is necessarily a measure. It’s impossible to know the complete domain of an aggregate field since the domain depends on the partitioning dimensions, of which there could be infinitely many. Then since the average colour is NOT being sent to the group by clause, we have just one continuous line:

                             

                            line2.PNG.png

                             

                            What’s happening in the first example is that there are no blue  data points where there are  orange data points and vice versa, so when the area chart fills down, the colours don’t overlap.

                             

                            In the second case however, the blue fills down and in some sense ‘covers up’ all of the orange points. 

                             

                            So it’s not a bug, it has much more to with the way databases handle aggregate vs row level calculations.