11 Replies Latest reply: May 9, 2012 2:31 AM by Rod West RSS

    Discoverer Formula Column

    christm31
      Hi

      Hoping you can help with a formula column I need to create.

      The situation is this:
      It's for a HR head count report which is split by Employment category.
      The issue is for multiple assignments, where in this report - I only want to count each person once, but not necessarily their Primary Assignment.

      If we assume we have 3 employees, one of which has 2 assignments in different Employment Categories - each assignment has a head count of 1:

      Employee A - Assignmnent AA - Employment Category A
      Employee B - Assignment BB - Employment Category B
      Employee C - Assignment CC - Employment Category A
      Employee C - Assignment CD - Employment Category B.

      As normal, the summed report of headcount would show

      Employment Category A - 2
      Employment Category B - 2

      However, the way I need it to work is that the report will prioritise each employment category so that if an employee already has a record in Employment Category A, it won't count the record in Employment Category B. If the employee only has a record in Employment Category B, it will show though.

      So the result I want is:
      Employment Category A - 2
      Employment Category B - 1

      Is there a way for a formula to work out this priority and so only sum for Employment Category B where there doesn't exist an Employment Category A record for the same employee?

      I hope I have explained this ok.

      Many thanks

      Martin
        • 1. Re: Discoverer Formula Column
          christm31
          Anyone got any ideas?
          Would be much appreciated.

          Thanks

          Martin
          • 2. Re: Discoverer Formula Column
            Rod West
            Hi,

            There is not way for a simple formula to do this. However, assuming that you want the employee to count only in the first employee category then here are 2 approaches could try:

            1. Create a complex folder in the EUL that contains the Max(Employee) and Max(Employment Category) so that you only have one record returned for each employee. Then create your workbook from this folder by selecting the max(employment) item and the count of the Max(employee) item.

            2. Create a workbook selecting Max(Employee) and Max(Employment Category) from EUL. Then create a calculation containing:

            count(max_employee) over (partition by max_employment_category)

            and a rownumber calculation using:

            row_number() over (partition by max_employment_category)

            Then add a condition rownumber=1

            Rod West
            • 3. Re: Discoverer Formula Column
              christm31
              Hi

              Thanks for that.
              I have created a sheet with employee numer and max(employment category) which is bringing back the correct detail I'm looking for.
              However I can't find a way to count the employees to get the right output.
              If I try and do a count(max(employment_category)), I get an error saying Error in formula - Nested aggregate functions are not allowed.

              Martin
              • 4. Re: Discoverer Formula Column
                Rod West
                Hi,
                I get an error saying Error in formula
                You cannot have 2 levels of aggregation in your workbook. So either you put the max(employment_category) and max(employmee) in a complex folder in the EUL as described in option 1 in the post above or you put the max(employment_category) and max(employmee) in the workbook and use an analytic function to do the count as described in option 2.

                Rod West
                • 5. Re: Discoverer Formula Column
                  christm31
                  Hi

                  Thanks for that - I now have it working.
                  I am however having issues with Totals.
                  They are fine in Discoverer Plus but I need to run this report in Desktop.
                  In Desktop, I have totals for rows and totals for columns for both HeadCount and FTE.
                  Initially, none were showing.
                  After changing the aggregation option from 'Default aggregation behaviour' to 'Show the sum of the values', I now get everything except the grand total for HeadCount.
                  Example below
                  ______________Head Count____________________________FTE
                  ________Asg Cat 1______Asg Cat 2____Total______Asg Cat 1____Asg Cat 2______Total
                  Org1________2____________3__________5__________2____________3___________5
                  Org2________1____________2__________3__________1____________2___________3
                  Total________3____________5_____________________3____________5___________8

                  As you can see, I'm getting every total except the total of '8' for HeadCount but I am getting it for FTE.
                  I know this is often due to folder joins, but FTE and HeadCount are coming from the same folder, yet FTE is working and HeadCount isn't.

                  Any ideas?

                  Many thanks

                  Martin

                  Edited by: christm31 on May 4, 2012 10:27 AM
                  • 6. Re: Discoverer Formula Column
                    christm31
                    All my formatting went to pot there, so it's hard to see what I mean.

                    I'm basically getting all my totals except the grand total for Head Count (the sum of the sums if you know what I mean)

                    Thanks

                    Martin
                    • 7. Re: Discoverer Formula Column
                      Rod West
                      Hi,

                      Make sure you have the Discoverer preference AllowAggregationOverRepeatedValues set in the registry.

                      Also try using cellsum instead of sum.

                      Rod West
                      • 8. Re: Discoverer Formula Column
                        christm31
                        Thanks - am using cellsum already.
                        Set the registry setting as advised but afraid it's made no difference.

                        Thanks

                        Martin
                        • 9. Re: Discoverer Formula Column
                          Rod West
                          Actually, for the totals you might have more success with sum rather than cellsum.

                          Rod West
                          • 10. Re: Discoverer Formula Column
                            christm31
                            Hi

                            Have tried sum and cellsum but neither work.
                            I have found that it's only a problem because the column I'm trying to sum is a COUNT_DISTINCT column.
                            If I change it to just a COUNT column, my total works fine - however gives the wrong value as I need to do a COUNT_DISTINCT to get the value I'm looking for.

                            Any ideas?

                            Thanks

                            Martin
                            • 11. Re: Discoverer Formula Column
                              Rod West
                              Hi,

                              I am not sure why you need to use COUNT_DISTINCT as you should have Max(Employee) and Max(Employment Category) in your workbook or folder already making the rows unique. Could you give some details of the calculations you are using in the folder/workbook?

                              Rod West