Averaging Monthly Sum in Crosstab

[Rod]

I have a crosstab tab that sums up the amount sold during a month. This works very well. I want to average the monthly sum but only using month that have a non-zero amount sold.

For example:

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.

ROD>

[Peter]

Hi ROD,

Our suggestion is to use a conditional expression as shown in the following example:
= Avg(IIF(Fields.Value<>0, Fields.Value, null))

All the best,
Peter
the Telerik team

[Rod]

Peter,

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.

Thank you,
ROD>

[Peter]

Hi Rod,

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)

namespace Telerik.Reporting.Examples.CSharp
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using Telerik.Reporting.Expressions;

    class CountX : IAggregateFunction
    {
        List<int> validMonths;

        void IAggregateFunction.Init()
        {
            this.validMonths = new List<int>();
        }

        void IAggregateFunction.Accumulate(object[] values)
        {
            var month = (int)values[0];
            var value = (int)values[1];
            if (value > 0)
            {
                if (!this.validMonths.Contains(month))
                {
                    this.validMonths.Add(month);
                }
            }
        }

        object IAggregateFunction.GetValue()
        {
            return this.validMonths.Count;
        }

        void IAggregateFunction.Merge(IAggregateFunction aggregateFunction)
        {
            var countX = (CountX)aggregateFunction;
            foreach(var month in countX.validMonths)
            {
                if (!this.validMonths.Contains(month))
                {
                    this.validMonths.Add(month);
                }
            }
        }
    }
}

Kind regards,
Peter
the Telerik team

[Rod]

Peter,

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:

=Sum(Fields.MTWeight)/CountDistinct(Fields.RequiredDate.Month)

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.

ROD>

[May Zin]

Hi!

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.