Product May Jun Jul Avg
123 10 9 8 9
124 15 - 10 12.5
125 6 10 12 9.333
126 - - 7 7
So product 124 is divided by 2 instead of 3 and product 123 divided by 1.
I have looked at the forums and used google but I cannot figure out how to do this. Any help would be greatly appreciated.
5 Answers, 1 is accepted
Our suggestion is to use a conditional expression as shown in the following example:
= Avg(IIF(Fields.Value<>0, Fields.Value, null))
the Telerik team
I understand what you are proposing, what I cannot seem to figure out is how to count the non-zero columns. I am able to use EXEC to get the total sum for the row just fine.
Please see the tonnagereport.jpg. I need to count the non-zero columns in the red circled sum for my average. I cannot seem to access that value easily.
You can accomplish your requirement with a custom aggregate function that counts only the months that have value bigger than zero as shown the following example:
= Sum(Fields.MTWeight)/CountX(Fields.Month, Fields.MTWeight)
the Telerik team
Thank you. I was making this much more complicated that it had to be. I was able to get the result I needed with a much more simplified expression.
I added a column to the right outside of the group and used the following expression:
The CountDistinct works since no data will be returned for that month if there are no sales. I have not tested this but there could be an issue if more than 12 months of data is pulled which I think would be unusual for this client.
It was your formula for accessing the month field that gave me the idea. Thank you so much for the super quick responses. I will have one happy client now.
This example is very good answer for us but I have a few difficulty for use this example.
When I use this example on crystal report, I face the error on avg formula.
IIF(Fields.Value<>0, Fields.Value, null)
Above the formula, How can I write to attach "the average function" on crystal report?
When I wirte, I face the error "Does not match with avg formula".
So Give me guideline how to write the formula on the crystal.
Thanks. I hope the reply letter from you.