3 Replies Latest reply: Oct 24, 2013 11:52 AM by Raimund Leussink RSS

    Distribution chart

      Hello,

       

      I like to add a chart with a distribution (scatter or histogram): X rounded meeting time (read categories) Y count of employees with that average meeting length. What I already have in a chart: the rounded average meeting length with employee as dimension. See underneath.

       

      Knipsel.PNG.png

       

      Formula for the average meeting length (the "if" statement has to do with time registrered not related to any meeting, which would pollute the average meeting time).

       

      =ROUND((SUM(IF(Meeting.Meetings>0,Meeting.Time))+SUM(IF(Meeting.Meetings>0,Meeting.BreakTime))+SUM(IF(Meeting.Meetings>0,Meeting.Other)))/SUM(Meeting.Meetings),30)

      (1 records has the number of meetings incl. time, breaktime and other time per employee per interval > 1 employee can relate to many records)

       

      What I would like is to present: the numbers as a distribution like the underneath one.

       

      Knipsel2.PNG.png

      (don't like to say this, but the chart above was made in Excel in a couple of seconds > individual averages exported to Excel > pivot table on the length > chart with X distinct lengths, Y count)

       

      What I have trouble with: getting the distinct rounded meeting lengths on the X-axes and also the count of the rounded averages (trouble there is that first the average has to be calculated per employee, after which a count has to be made of those averages).

       

      Has anyone a suggestion how I could realise this (frontside only if possible)? Many thanks for any thoughts.

       

       

       


        • Re: Distribution chart
          Michele Barini

          Hi,

          maybe you could use the formula for the average as calculated dimension and then an expression having the count.

          • Re: Distribution chart
            Gysbert Wassenaar

            Perhaps a calculated dimension class(rangesum(Meeting.Meetings,Meeting.BreakTime,Meeting.Other),30) and count(Meeting.Meetings) as expression. Post sample data if you need more help.

              • Re: Distribution chart

                Hi, the class function did help, thanks for the suggestion.

                 

                Next step is getting the per employee dimension integrated which can than be counted. Class now results in a single class (with the overall average, since the employees are not taken into account). Basically 1 bar in the graph. I'll have a look if I can create some sample data (actual data is >100K records with ~200K meetings).

                 

                A few sample records  (not actual records, but same problem)

                 

                INPUT records
                EmplMeetingTimeMeetingBreakMeetingOtherMeetingMeetings
                120050302
                125040401
                140030301
                260075603
                220050302
                230030301
                380050301
                340060402
                310050301
                345090303
                430050302
                520050302
                525040401
                540030301

                 

                The chart underneath is the result I'm aiming for.

                Knipsel3.PNG.png