GridLabelAt - For Avarage

[Former Member]

Hey all,

I'm tryng to do an avarage on sales table.

The table is designed that I have months on the top (column) jan, feb... dec

and rows are devided by years

2010

2011

2012

I need to do an avarage for each year.

and for 2012 I counted the number of the month month(CurrentDate())

and will devide by it.

I have a problem using GridLabelAt when I'm trying to read lable '2012' from my table

I can't find the correct systax for it, this is my code:

if GridLabelAt(year({Sap_Crystal_Logistic;1.DocDate})=2012) then

(

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex)

)

else 99999

Help me correct it please..

Also if you have a better method I would like to hear it.

Thanks

[jyothirmayee_s · Accepted solution]

Hi,

For the Yearly average -- Right click the first row in the 'Total' column (by default, this is the left-most column), select 'Edit Summary', and choose 'Average' from the 'Calculate this summary' combo-box. This assumes that the cross-tab always includes 12 months of data, which can be easily done by setting an appropriate record-selection formula.

If you have Crystal Reports 2008, you can add a calculated column.

Here are the steps:

1. Select the second column's header and choose Calculated Member | Insert Column.
2. Right click the first cell in the new column and select Calculated Member | Edit Calculation Formula
3. Enter the following in the formula:

GridValueAt (CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex)+ GridValueAt(CurrentrowIndex,CurrentColumnIndex-2,CurrentSummaryIndex)

4. Change the column header's text by right clicking the header and choosing Calculated Member | Edit Header Formula'

Edit the formula as per your need.

Here is the link on how to create a calculated member column.

http://www.crystalkeen.com/articles/crystalreports/crosstabcalcmember.htm

Let me know if you need more details.

Thanks,

Jothi

[Former Member · Accepted solution]

Hey,

Thank you for the reply,

but I have a different issue where I have lines that I need to divide in the current month count.

Example:

2010 will be divided by 12 months

2011 will be divided by 12 months

Current year (2012) will be divided by 6 months because we are in June.

How can I do that formula?

In SQL I can do that:

(CASE when YEAR(T0.DocDate) = YEAR(GETDATE()) Then Month(GETDATE())

  else '12'

  END) AS 'CurDate',

  Quantity/(CASE when YEAR(T0.DocDate) = YEAR(GETDATE()) Then Month(GETDATE())

  else '12'

  END) AS 'Avarage'

I can't do it in Crystal..

Can you advise?

[former_member260594 · Accepted solution]

Hello Heli,

If you are using a crosstab and the rows are based on the month number the crosstab average summary is smart enough not to take into account the months that do not have a value. So if for 2012 there is only data til June then it will automatically average only the 6 months.

Can you confirm that it is or is not doing this? Are there actual 0 values for July to December in your database?

If there are then you can filter those out in the record selection with something like {datefield} <= currrentdate.

Regards,

Graham

[Former Member · Accepted solution]

Hey Graham,

I am using crosstab and it did divide the year 2012 in 12 months,

it didn't ignore the values with 0 in them from July to Dec.

Can you please send me the code for the filter I need to do in the crosstab formula?

Thanks,

Heli

[former_member260594 · Accepted solution]

Hi Heli,

The record selection formula would be something like {your date field} <= currentdate

Graham

[jyothirmayee_s · Accepted solution]

Hi Heli,

Are you only looking for a Summary for Current Year and not the previous years?.

Then my solution may not be accurate.\\

Thanks

Jothi

[Former Member · Accepted solution]

Hey Jyothirmayee,

You are right, I need that previous years will be divided by 12,

and current year will be divided by current months..

Do you have a solution for me?

Thanks

Heli

[jyothirmayee_s · Accepted solution]

Hi Heli,

Then you can still use the Calcualted member. Did you tried btw?.

Thanks,

Jothi

[jyothirmayee_s · Accepted solution]

Hi Heli,

There is another solution.

Go to Crosstab expert select the Measure that you are displaying as into Summarzed field and change the Summary to "Average". If you want to show the average of each year then "Do not Suppress Row Grand Total".

Hope this helps.

Thanks

Jothi

[Former Member · Accepted solution]

year  /  month	jan	feb	mar	apr...	total	avg
2010	12	10	5	10	37	sum/12 (months)
2011	12	10	5	10	37	sum/12 (months)
2012	10	10	0	0	20	sum/6(current months count)

This is my table, imagine there is 12 moths their, every year the average divided by 12 months,

and current year divided by 6 because we are in June..

Cross tab average does not ignore the zeros and divides 2012 in 12 instead of 6.

How can I tell it to look at the years and if the year = (current year)

Then divide it by the months count (6)

Thanks     

[abhilash_kumar · Accepted solution]

Hi Heli,

Try this as the Calculation Formula for the Calculated Member:

if Gridlabelat("Sheet1_.Year",1) = 2012 then
(
    (GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex))/month(currentdate)
)
else
(GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex) )/12

Let me know how this goes!

-Abhilash

[jyothirmayee_s · Accepted solution]

Hi Abhilash,

How about a user running the same report in 2013. Does developer has to change the formula again to 2013 or current yr?.

Can the current year be dynamic?. like

if Gridlabelat("Sheet1_.Year",1) = currentyear

....

Please advice

Thanks

Jothi.

[abhilash_kumar · Accepted solution]

Hi Jothi,

Oh yes, I did not take that into account.

Thanks for pointing that out; yes, it can be made dynamic.

So, you can do something like this then:

if Gridlabelat("Sheet1_.Year",1) = year(currentdate) then

(

)

else

(

)

-Abhilash

[jyothirmayee_s · Accepted solution]

Thanks Abhilash

Hi Heli,

Here you go with the excellent solution provided by Abhilash.

Post back if you still facing the issue.

Thanks,

Jothi

[Former Member · Accepted solution]

Thanks guys,

if Gridlabelat("Sap_Crystal_Logistic;1.DocDate",1) =  YEAR(currentdate)

It still Gives me problem when I write YEAR(currentdate) I get an error "a datetime is requires here"

and when I write only (currentdate), I can save it but it does nothing..

Do you know why?

Heli

[abhilash_kumar · Accepted solution]

Hi Heli,

How are the dates stored in the SAP system? Are they numbers, characters or dates?

-Abhilash

[Former Member · Accepted solution]

Hey Abhilash,

I created a stored procedure,

and inside that stored procedure I created a temperory table.

The DocDate field is defined in SQL as "DateTime"

Thanks,

Heli

[Former Member · Accepted solution]

Hey,

I did this:

**************************************

@CurDate = year(CurrentDate)

@X = month(currentdate)

************************************

if

year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate",1)) = ({@CurDate})

then

(

(GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+ 

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex))  /({@X})

)

else

99999

But now it divides everything by the current month (X).

Thanks

Heli

[abhilash_kumar · Accepted solution]

Hi Heli,

Try this:

If year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate",1)) = ({@CurDate}) then
(  
    (GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex))/({@X})  
)  
else  
(GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Feb"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Mar"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Apr"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("May"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jun"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jul"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Aug"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Sep"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Oct"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Nov"), CurrentSummaryIndex)+  
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Dec"), CurrentSummaryIndex) )/12 

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

It's the same Idea as I did.. I just wrote 9999

It is still divides everything by the current month (X). (June = 6)

So instead of dividing 2010 and 2011 by 12

and 2012 by 6

it divides everything by 6..

Heli

[Former Member · Accepted solution]

maybe it's because:

year(CurrentDate) gives me a result of 2,012.00

I want it to show me only 2012

How can I do it in the code and still keep it as a number?

I can't use ToText becuase it has to be a number.

Thanks

[abhilash_kumar · Accepted solution]

Hi Heli,

It doesn't matter as long as it's a number.

So, 2,012.00 is same as 2012.

Anyway, I think I see what's going wrong here. The Gridlabelat() function you're using is only looking at the first row when it should actually evaluate for each row.

Change the first line in the code to:

If year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate",currentrowindex)) = year(currentdate) then

(

)

else

(

)

I added currentrowindex to the formula.

Let me know how this goes!

-Abhilash

[Former Member · Accepted solution]

Thank you Abhilash!

[abhilash_kumar · Accepted solution]

Glad that worked!

Until next time, take care!

-Abhilash

[Former Member · Accepted solution]

Hey Abhilash,

I added another column, I want to do in this column a percent change between 2010-2011 and 2011-2012

The formula suppose to look like this

(2012-2011)/2011

(2011-2010/)2010

How do you suggest doing it?

Heli