none
Need to include nulls as zeroes in ProClarity average column total

    Question

  • Hi, I am experiencing difficulty with a column total in ProClarity. The data comes through with nulls on some rows (in the example, it is a product QA failure count and the value is only recorded when failures occur. Some rows have no failures and therefore a null). The total is an average of the column and be default exludes the null rows, I have a requirement to include the nulls as zeroes and I do not know how to do this. Is this acheived by modifying the MDX in ProClarity or changing the calculated measures accordingly?

     

    This is an example of what I currently receive and also what I would like to achieve. Any ideas? My MDX is not up to scratch, so I may need help with that too.

     

    Current outcome Desired outcome
      AvgFailRate AvgFailRate
      5 3.75
    Prod1 2 2
    Prod2 Null 0
    Prod3 8 8
    Prod4 5 5

     

    Thanks, Charles.

     

    ps. I have tried changing the format string in the SSAS calculated measure to "0;0;0;0" which displays a zero when it is a Null but still appears to exclude them from the average total.

    Wednesday, November 12, 2008 9:30 AM

Answers

  • The Avg function in Analysis Services does not include null values in the denomintator so you would need to calculated this soft of average yourself. You would want to do something like the following:

     

     Measures.[FailureRate] / Count(Existing [Product].[Product].[Product])

     

    The EXISTING operator will return the set of all of the members from the [Product] level of the [Product] hierarchy in the [Product] dimension. You then divide your failures measure, by the count of this set of members.
    Thursday, November 13, 2008 1:29 AM
    Moderator

All replies

  • What's the value of 'NULL PROCESSING' property for your measure?

    Wednesday, November 12, 2008 10:01 AM
    Answerer
  • Hi pravin14u,

     

    If you are referring to the Non-empty behaviour property in the calculated measure, then nothing is selected in that specific dropdown - as the value is the result of a calculation and not just one single measure. In the Expression script of the calculated measure I have an iif() statement which checks if the value of another measure used as a divisor is either 0 or null and if so then null else the calculation result.

     

    If this is not what you are referring to, then please point me in the right direction regarding 'null processing'. Thanks.

     

    Charles.

    Wednesday, November 12, 2008 1:50 PM
  • Can you post your calculation please?

     

    Wednesday, November 12, 2008 4:16 PM
    Answerer
  • The Avg function in Analysis Services does not include null values in the denomintator so you would need to calculated this soft of average yourself. You would want to do something like the following:

     

     Measures.[FailureRate] / Count(Existing [Product].[Product].[Product])

     

    The EXISTING operator will return the set of all of the members from the [Product] level of the [Product] hierarchy in the [Product] dimension. You then divide your failures measure, by the count of this set of members.
    Thursday, November 13, 2008 1:29 AM
    Moderator
  • That is what I am doing, so I am happy that the individual rows are correctly reported in ProClarity and some rows are null.

     

    In the Column Totals in ProClarity (I believe this tally processing takes place inside ProClarity's Business Logic engine) the null rows are excluded from the Column Total.

     

    Have a look at the 2 grids in my first post and you'll see that if I replace the null with a zero, the column total does decrease.

     

    Charles.

     

    p.s. I need to clarify that our expression is in the form : [Measures].[FailureRate] / [Measures].[Product Count]

    But I think that amounts to the same as your example above.

     

     

    Thursday, November 13, 2008 9:53 AM
  •  Charles Venter wrote:

    That is what I am doing, so I am happy that the individual rows are correctly reported in ProClarity and some rows are null.

     

    In the Column Totals in ProClarity (I believe this tally processing takes place inside ProClarity's Business Logic engine) the null rows are excluded from the Column Total.

     

    Have a look at the 2 grids in my first post and you'll see that if I replace the null with a zero, the column total does decrease.

     

    Charles.

     

    p.s. I need to clarify that our expression is in the form : [Measures].[FailureRate] / [Measures].[Product Count]

    But I think that amounts to the same as your example above.

    No this is not the same.

     

    Your expression will return the same answers as the gird on the left, mine will do the grid on the right.

     

    I would guess that you are doing a distinct count of the productid from the fact table. A count based measure will not take null rows into account.

     

    Have a look at my previous answer and give it a try before you discount it. The expression I put in the my last post counts the dimension members regardless of whether or not they have associated facts.

    Thursday, November 13, 2008 12:41 PM
    Moderator