on 12-29-2013 7:32 AM
and folks.I figured out the answer.......................
For this we need to create two formulas
Formula1# save the record information in the arrayform [note: Variables should be declared as Shared type]
Formula2# sort the array and apply the manual median formula.
------------------------------------------------------------------------------------------------------
Formula1 as follows. we can also drop this formula in detail section for clarification
------------------------------------------------------------------------------------------------------
Whileprintingrecords;
shared numbervar array jan_value;
shared numbervar jan_counter;
IF
(
monthname(table.year)= "Jan"
)then
(jan_counter:= jan_counter + 1;
Redim Preserve jan_value[jan_counter];
jan_value[jan_counter] :=table.value;
)
else
0
--------------------------------------------------------------------------------------
formula2 as follows and it needs to be dropped in report footer only
--------------------------------------------------------------------------------------
Whileprintingrecords;
Shared numbervar array jan_value ;
Shared numbervar jan_counter;
numbervar Jcounter1;
numbervar Jcounter2;
numbervar Jtemp_arr;
numbervar J_middle;
numbervar Jmedian;
for Jcounter1 := ubound(jan_value) to 1 step -1 do //This loop sorts the array
(
for Jcounter2 := 1 to Jcounter1 - 1 do
(
if jan_value[Jcounter2] >jan_value[Jcounter2 + 1] then
(
Jtemp_arr := jan_value[Jcounter2];
jan_value[Jcounter2] := jan_value[Jcounter2 + 1];
jan_value[Jcounter2 + 1] := Jtemp_arr;
)
);
);
if ubound(jan_value) mod 2 = 1 then //this finds the Median
(
J_middle := (ubound(jan_value)+1)/2;
Jmedian := jan_value[J_middle];
)
else
(
J_middle := ubound(jan_value)/2;
Jmedian := (jan_value[J_middle] + jan_value[J_middle+1])/2
);
------------------------------------------------
Finally the issue is resolved
----------------------------------------------------
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Phani
Are you committed to using an array for your report? It may be just as easy to write a few formulas to collect the data in variables.
For example . . .
Create a formula named _detail_variables:
Whileprintingrecords;
Shared numbervar jan_value;
Shared numbervar feb_value;
Shared numbervar mar_value;
. . . . //other months
Shared numbervar jan_counter;
Shared numbervar feb_counter;
Shared numbervar mar_counter;
. . . //other months
If ([month]=”jan” and [flag]=”TRUE) then
(jan_value:= jan_value + [value];
jan_counter:= jan_counter + 1);
If ([month]=”feb” and [flag]=”TRUE) then
(feb_value:= feb_value + [value];
feb_counter:= feb_counter + 1);
If . . . . //other months
drop the above formula into the details section.
Then create 12 “median” formulas – one for each month.
@jan_median
Whileprintingrecords;
Shared numbervar jan_value;
Shared numbervar jan_counter;
If jan_counter>0 then
jan_value / jan_counter else 0
drop these 12 formulas into the report footer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Mike Lundquist ,
Lets take only Jan_named _detail_variables
///////////
Whileprintingrecords;
Shared numbervar jan_value;
Shared numbervar jan_counter;
If ([month]=”jan” and [flag]=”TRUE) then
(jan_value:= jan_value + [value]; // Here I want to store the values in the array format. But Jan_value returns SUMMATION value
jan_counter:= jan_counter + 1); // Returns perfect count
//////
But the above formula returns only Jan_counter
would you mind could you elaborate your coding for one month(January) in detail. Please..
Right now I am practicing like this:
1. Dynamic variable # store the Jan month values in an array format
2. Sorted array# sort the Jan month values and calculate median
Hi Phani
my solution doesn't use arrays . . . . unfortunately, after re-reading your post . . . my solution provides the mean - not the median
for January you need two formulas:
_detail_variables
jan_median
copy the code from my previous post into the formulas
drop the 1st formula into the details section
drop the 2nd formula into the report footer
if you are committed to using arrays, then refer to Abhi's posts
Hi
Best way to do this is
--Insert same report as sub report in your report footer.
--Open the sub report and insert Median values at group footer level by right clicking on value and insert summary--Median
--Suppress all detail and other sections except Group footer.
--Now go in main report section expert and based on Flag value suppress report footer.
Thanks,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Lets forget about the flag conditon: i was unable to assign the Value field label to the below formula
numbervar counter1;
numbervar counter2;
numbervar temp_arr;
numbervar i;
numbervar middle;
numbervar median1;
numbervar array arr:={@values}; //here its accepting only the last record in the detail section. I need sincere advise here
for counter1 := ubound(arr) to 1 step -1 do //This loop sorts the array
(
for counter2 := 1 to counter1 - 1 do
(
if arr[counter2] > arr[counter2 + 1] then
(
temp_arr := arr[counter2];
arr[counter2] := arr[counter2 + 1];
arr[counter2 + 1] := temp_arr;
)
);
);
if ubound(arr) mod 2 = 1 then //this finds the Median
(
middle := (ubound(arr)+1)/2;
median1 := arr[middle];
)
else
(
middle := ubound(arr)/2;
median1 := (arr[middle] + arr[middle+1])/2
);
I guess you found that formula here:
http://scn.sap.com/thread/3270155
Andrew and Graham have a few other suggestions there that you might want to take a look at too.
-Abhilash
The following line in the code is Not supposed to be the way you've interpreted:
numbervar array arr:={@values};
You need to create a separate formula that 'builds' the array. Then refer to the array variable in the above formula. The formula you've pasted above 'sorts' and 'finds out' the Median - that's it.
The line should only be:
numbervar array arr;
If you look at the thread again, Graham suggests something simpler for the crosstab.
-Abhilash
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.