8 Replies Latest reply: Jul 21, 2011 2:18 AM by ben weeks RSS

    Averages in a graph

      Hi

       

      I am hoping someone can help...

       

      I am trying to calculate Utilisation %'s. I currently have a table, using dimensions: Company, Centre, Month, Week, Employee. Off the back of this, I have a breakdown of hours (Presence, Sick, Project Hours) as my expressions

       

      I also have an expression in this table to calculate a utilisation %:

      sum(if(BOOKING_TYPE=Direct,HOURS))

      / (sum(distinct if(Date,PRESENT_HOURS))

      + sum(distant if(Date,OVERTIME_HOURS)))

       

      e.g.

      Company     Centre       Month      Week    Employee    Presence   Overtime      Direct       Utilisation %

                                                                                            66.5         56.75       147.25            79.06%

      13                  769          May          18        123456           29.5         25.5           30.5             55.45%

      13                  769          May          19        123456           29.5         17.5           38                80.85%

      13                  769          May          20        123456           37            3               37                92.50%

      13                  769          May          21        123456           37            10.75        41.75            87.43%

       

      Against the Utilisation expression, I have the Total set to 'Avg of rows' to provide the 79.06% figure

       

      What I am now trying to do, is to provide a line graph, tracking the utilisation % per week/month. I want to be able to do this at Centre level, but if I did select an Employee, then I would expect to see their Utilisation %'s over the selected period

       

      For this, I have used the dimensions: Date and Centre, whilst using the same expression calculation as above. Based on the results, the average calculation for May becomes 119.47% instead of 79.06%

       

      I can replicate this figure within the table (I am using the expression default as the Total option). In the graph itself, I have got the Average Total of rows selected, but it still shows 119.47%

       

      Any help would be appreciated

       

      Thanks

      Ben

        • Averages in a graph
          Daniel Rozental

          Can you post an example application?

           

          Or at least post the expression you're using.

            • Averages in a graph

              Hi,

               

              The expression I am using is:

               

              sum(if(BOOKING_TYPE=Direct,HOURS))

              / (sum(distinct if(Date,PRESENT_HOURS))

              + sum(distant if(Date,OVERTIME_HOURS)))

                • Averages in a graph
                  Hamish Donald

                  Hi,

                   

                  It could be that the 'distinct' in the expression is causing a problem - the 'Presence' total of 66.5 seems too low because it's only adding the distinct values of 29.5 and 37, rather than double that.  This would generate the 119.47% figure,

                   

                  Regards,

                   

                  HD

                    • Averages in a graph

                      Hi HD,

                       

                      Thanks for the reply

                       

                      The individual calculation for presence hours now works (sum the total, instead of the expression total)

                       

                      The reason I need a distinct reference is because I have joined two separate tables together. One includes Project hours, and the other the attendance. The problem I have, is that all Project rows need to have their hours counted. However, the attendance values should only be calculated once per the date (otherwise I end up with more attendance hours)

                       

                      In theory, the calculation should be:

                      Direct Hours / (Presence + Overtime) - in this case: 147.25 / (133+56.75)*100 = 77.60%

                       

                      Any idea?

                      Thanks so far!

                        • Averages in a graph
                          Hamish Donald

                          Tricky to say without seeing the context, but you could try using the Aggr(expression,dimensions) syntax to mimic the behaviour of the pivot table in the chart,

                           

                          e.g. force the expression to calculate each of the three parts of the sum per week/employee combination (which I'm assuming are not included in the chart)

                           

                          perhaps like ,

                           

                          sum( Aggr( if(BOOKING_TYPE=Direct,HOURS), Week, Employee)  )

                           

                          /  etc..

                           

                          but it may be better to post an example .qvw as a data modelling problem and try and simplify the underlying tables first (other contributors will have had the same modelling issues, no doubt),

                           

                          Regards,

                           

                          HD