This is a migrated thread and some comments may be shown as answers.

Averaging Monthly Sum in Crosstab

5 Answers 436 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Rod
Top achievements
Rank 1
Rod asked on 12 Nov 2012, 07:13 PM
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>

5 Answers, 1 is accepted

Sort by
0
Peter
Telerik team
answered on 13 Nov 2012, 09:13 AM
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

HAPPY WITH TELERIK REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

0
Rod
Top achievements
Rank 1
answered on 13 Nov 2012, 08:25 PM
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>
0
Peter
Telerik team
answered on 14 Nov 2012, 11:32 AM
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

HAPPY WITH TELERIK REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

0
Rod
Top achievements
Rank 1
answered on 14 Nov 2012, 05:16 PM
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>

0
May Zin
Top achievements
Rank 1
answered on 23 Dec 2016, 12:00 PM

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.

 

Tags
General Discussions
Asked by
Rod
Top achievements
Rank 1
Answers by
Peter
Telerik team
Rod
Top achievements
Rank 1
May Zin
Top achievements
Rank 1
Share this question
or