This discussion is archived
11 Replies Latest reply: May 9, 2012 12:31 AM by RodWest RSS

Discoverer Formula Column

christm31 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    Anyone got any ideas?
    Would be much appreciated.

    Thanks

    Martin
  • 2. Re: Discoverer Formula Column
    RodWest Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    RodWest Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    RodWest Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    RodWest Guru
    Currently Being Moderated
    Actually, for the totals you might have more success with sum rather than cellsum.

    Rod West
  • 10. Re: Discoverer Formula Column
    christm31 Newbie
    Currently Being Moderated
    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
    RodWest Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points