locked
aggregate functions in another aggregate function RRS feed

  • Question

  • Hello,

    I have

    =Count(Fields!Date_Result.Value) in one field as summarization of the number of cells not blank in the column.

    =Count(Fields!SCOMAdmin.Value) in another field as summarization of the number of cells not blank in the column.

    Now I would like to have the percentage =Count(Fields!SCOMAdmin.Value) / =Count(Fields!Date_Result.Value) in another field.

    How to do this?

    I cannot use aggregate functions in another aggregate function.

    Should I have names for the fileds and work  with the fields names like =Sum(TotalSCOMAdmin/TotalResults)? any way to make this works ?

    Thanks,

    DOm


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, October 17, 2012 3:48 PM

Answers

  • =Count(Fields!SCOMAdmin.Value) / (Count(Fields!Date_Result.Value) + Count(Fields!SCOMAdmin.Value)) is percent SCOMAdmin
    =Count(Fields!Date_Result.Value) / (Count(Fields!Date_Result.Value) + Count(Fields!SCOMAdmin.Value)) is percent Date_Result

    The second = will cause an error and is not needed. Also that ratio doesn't get you a percentage unless Date_Result represents the total and SCOMAdmin is a subset of that. The above formulas should work and do not embed one aggregate in another. They are siblings in a single formula.

    • Marked as answer by Felyjos Wednesday, October 17, 2012 4:16 PM
    Wednesday, October 17, 2012 4:01 PM

All replies

  • =Count(Fields!SCOMAdmin.Value) / (Count(Fields!Date_Result.Value) + Count(Fields!SCOMAdmin.Value)) is percent SCOMAdmin
    =Count(Fields!Date_Result.Value) / (Count(Fields!Date_Result.Value) + Count(Fields!SCOMAdmin.Value)) is percent Date_Result

    The second = will cause an error and is not needed. Also that ratio doesn't get you a percentage unless Date_Result represents the total and SCOMAdmin is a subset of that. The above formulas should work and do not embed one aggregate in another. They are siblings in a single formula.

    • Marked as answer by Felyjos Wednesday, October 17, 2012 4:16 PM
    Wednesday, October 17, 2012 4:01 PM
  • Hi Tim,

    Yes SCOMAdmin is a subset of Date_Result. I am counting the number of non-blank in SCOMAdmin column and compare to the number of non-blank in Date_Result column (two different columns) this should give me the percentage of work done on SCOMAdmin vs the number of day in the month filled in Date_Result.

    Let me try your formulas

    Thanks,

    DOm


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager


    • Edited by Felyjos Wednesday, October 17, 2012 4:14 PM
    Wednesday, October 17, 2012 4:13 PM