2 Replies Latest reply: Sep 28, 2011 8:51 AM by Jason Newman RSS

    Unwanted document level calculation

      I logged a question last week which Jason Michaelides kindly supplied me with the following solution:

      COUNT({<Employee={"=SUM({<[Time category group] ={Productive,Billable}>} [Project time])/SUM([Project time])< 0.49999"} >} DISTINCT Employee)


      i.e. supply the number of employees where their productive and billable projects amount to less than 50% of their work (each employee will have a number of different entries (the projects))


      This works perfectly when I displayed the answer in a text box. I have now been asked to map this figure over different periods and place them in a bar chart (Dimension axis = period and Expression axis = the above calculation)

      I have defined a variable vRed as the above calculation (without an equals sign at the front) and used the following calculation on the chart (without an equals sign at the front):

      num($(vRed)/Count(Distinct Employee),'##.#%')


      The problem I have is when the calculation is placed in a graph it first calculates vRed at a document level and then applies the  Count(Distinct Employee)at the period level.

      I am not very experienced with set analysis, but from what I have read the ‘=’ is an instruction to calculate at the document level, so I am assuming the ‘=’ in the middle of the vRed calculation is causing my problem?

      If this is the case, how do I get round this problem? I have seen Do While loops, but I am not sure that these are used in expression calculations.




        • Unwanted document level calculation
          Stephen Redmond



          If vRed is defined without an = sign then it should not be calculated at the document level.


          To check what is happening, change your chart to a straight table and make the label for the express blank (i.e. it defaults to <use expression>).  When the straight table displays, the actual expression that is being used - after the dollar expansion - will be visible and that should help you troubleshoot what is happening.







            • Unwanted document level calculation

              Hi Stephen,


              Thanks for your suggestion, but unfortunately the calculation just appears as if I had written it ‘long hand’.


              The way it is calculating the answer is as follows:

              - It looks at the data for all periods and calculates how many Employees are <50%, say 10 people.

              - It then looks at the specified period and checks how many of those 10 people have data in that period – say 8.

              - It will then use the 8 as the numerator and divides it by the number of Distinct employees in that period


              Obviously I want it to use the calculated count of employees <50% for the specified period to be the numerator.