Computed column (average)

Use datediff functions. If you provide sample data (CSV) and version of BIRT. I could probably build an example in a few minutes.

Mica
 
CSV link
BIRT 4.5
 
Martin
 
<a data-ipb='nomediaparse' href='https://dl.dropboxusercontent.com/u/22288628/computed_average.csv'>https://dl.dropboxusercontent.com/u/22288628/computed_average.csv</a>

Is this how really how you get the data? No dates?

Mica
 
No, I just want to simplify.
Find a new csv here
 
<a data-ipb='nomediaparse' href='https://dl.dropboxusercontent.com/u/22288628/computed_average1.csv'>https://dl.dropboxusercontent.com/u/22288628/computed_average1.csv</a>
 
Martin

Thanks. This second CSV is no harder. That said the challenge is not as easy as I thought, as this requires to passes on the data.

Mica
 
You have some advices for me?
 
Thank you for your time
 
Martin

Working on many projects, hope to get to this today if not Tuesday,

Like Mica indicated earlier, this isn't an easy task.
 
I'm sure there is a much better way to accomplish this, however I've attached an example to work from.
This makes the calculations based on your last CSV upload: <a data-ipb='nomediaparse' href='https://dl.dropboxusercontent.com/u/22288628/computed_average1.csv'>https://dl.dropboxusercontent.com/u/22288628/computed_average1.csv</a>
 
I loaded the CSV in the open script of the "My Data Set" scripted data set.
You will need to change the path to point to your CSV file.
 
All the code to accomplish this task is in the scripted data set called "Output Data Set".
In the beforeOpen statement, we query the data from "My Data Set" and build an array object to store the values in for later use.
 
Then in the fetch statement of the scripted data set called "Output Data Set" we loop through the array object built in the beforeOpen statement and accomplish the math required to produce the output desired.
 
Hopefully this is helpful

Thanks Matthew! My idea was to calculate on the onCreate event and load an array, then display the array on the onRender event. This will only work if you are not using the runandrender task, but using separate task for run then render,
 
Martin, let me know if the above solution works for you or if you want my solution

Thank you to both of you.
With some lag-lead queries, I was also able to have the number I want. But since I create a join dataset with all day of a month, I would like to be able to fill empty rows below a value... How can I do it? In next example, fill with value 8 for second an third of july... Computed column is my first choice
 
2016-07-01 8
2016-07-02
2016-07-03
2016-07-04 12
 
Martin

Try this:
 
In your design in the beforeFactory put this code:
<pre class="_prettyXprint">
lastValue=-1</pre>
;
Then create a data item with this in the expression:
<div>
<pre class="_prettyXprint">
if (row["computed_average"]==null) {
lastValue;
}
else {
row["computed_average"];
}</pre>
</div>
<div>In the onCreate event of the data item put this code:</div>
<div>
<pre class="_prettyXprint">
if (lastValue==-1 || this.getRowData().getColumnValue("computed_average")!=null) {
lastValue = this.getRowData().getColumnValue("computed_average");
}</pre>
</div>

You could just create a computed column and check the "Value" column for a value.
If it contains a value you can store it in a report variable or global variable to be used in the next null row.
 
Attached is an example, relative code below:
<pre class="_prettyXprint _lang-js">
//Place this code in a computed column of your data set
//Change the next line to point to the column with your values
var dataSetColumnWithValues = row["Value"]; //Change this row["name"] per your value column
if(dataSetColumnWithValues){ //Check for value within row
reportContext.setPersistentGlobalVariable("Computed_Value", ""+dataSetColumnWithValues); //Place value in a global variable
dataSetColumnWithValues; //Return value
}else{ //If no value in row
parseInt(reportContext.getPersistentGlobalVariable("Computed_Value")); //Use exising global variable value
}
</pre>

Matthew
 
Work fine.
But another little thing: have an idea to have this work with decimal and not only integer...?
 
2016-01-01 8.6
2016-01-02
2016-01-03 
...
 
Martin

Change parseInt to parseFloat:
<pre class="_prettyXprint _lang-js">
//Place this code in a computed column of your data set
//Change the next line to point to the column with your values
var dataSetColumnWithValues = row["Value"]; //Change this row["name"] per your value column
if(dataSetColumnWithValues){ //Check for value within row
reportContext.setPersistentGlobalVariable("Computed_Value", ""+dataSetColumnWithValues); //Place value in a global variable
dataSetColumnWithValues; //Return value
}else{ //If no value in row
parseFloat(reportContext.getPersistentGlobalVariable("Computed_Value")); //Use exising global variable value
}
</pre>

Matthew
 
It doesn't seem to "see" a zero number. On the attached file, you see on left the number 4 follow by 0 (the day after). On right side (Computed Column), it fill with 4 instead of 0... Idea?
 
<a data-ipb='nomediaparse' href='https://dl.dropboxusercontent.com/u/22288628/with_zero.png'>https://dl.dropboxusercontent.com/u/22288628/with_zero.png</a>
 
Thanks again
 
Martin

Try the following:
<pre class="_prettyXprint _lang-js">
//Use this calculation to generate the results
var dataSetColumnWithValues = row["Value"]; //Change this row["name"] per your value column
if(dataSetColumnWithValues != null){ //Check for value within row
reportContext.setPersistentGlobalVariable("Computed_Value", ""+dataSetColumnWithValues); //Place value in a global variable
dataSetColumnWithValues; //Return value
}else{ //If no value in row
parseFloat(reportContext.getPersistentGlobalVariable("Computed_Value")); //Use exising global variable value
}
</pre>

Matthew
 
Now, can you imagine a way to fill the "previous" empty column?
 
date number ave
1 21
2 
3 10 5.5
 
Fill date 2 field ave with 5.5 because 21-10 = 11 / 2 days = 5.5...
 
Martin