Eclipse Community Forums: BIRT » MEDIAN function on string???

Help

Home

Home » Archived » BIRT » MEDIAN function on string???(Is it possible to have median function on string?)

Show: Today's Messages :: Show Polls :: Message Navigator

MEDIAN function on string??? [message #735945]

Thu, 13 October 2011 07:28

Dejan Vujanic

Messages: 67
Registered: October 2011

Member

I have this column which represents difference between some actual and target start.Using DB2 functions timestampdiff,Floor and Mod I have this difference in format days--hours--minutes.
Now my customer wants median value of that column.
By using embedded MEDIAN function in BIRT I receive as result only "0".
In this case result should be 0 days 0 hours 1 minutes.
Is it possible to have median result in this format?

Thanks

Attachment: actual response.jpg
(Size: 45.98KB, Downloaded 1014 times)

Report message to a moderator

Re: MEDIAN function on string??? [message #736113 is a reply to message #735945]

Thu, 13 October 2011 15:44

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

You could always use birt date diff on the minute and then use another
computed column to convert it to a string. Have a look at the attached
example. Specifically look at the two computed columns on the dataset.

Jason

On 10/13/2011 3:28 AM, cankovicv wrote:
> I have this column which represents difference between some actual and target start.Using DB2 functions timestampdiff,Floor and Mod I have this difference in format days--hours--minutes.
> Now my customer wants median value of that column.
> By using embedded MEDIAN function in BIRT I receive as result only "0".
> In this case result should be 0 days 0 hours 1 minutes.
> Is it possible to have median result in this format?
>
> Thanks

Attachment: median.zip
(Size: 2.79KB, Downloaded 267 times)

Report message to a moderator

Re: MEDIAN function on string??? [message #741202 is a reply to message #736113]

Wed, 19 October 2011 09:36

Dejan Vujanic

Messages: 67
Registered: October 2011

Member

Hi Jason. Thanks for your reply and effort. You are really helping me.
You send me great example and I did it all like you. My computed column Median Minutes is always correct calculated but medianstring computed column is always empty.
I don't know why? I tried with some simple example just as Float to calculate from Median Computed column divided by 24 but that is also empty. Do you see maybe what is wrong? Here is all settings:

index.php/fa/4418/0/

Attachment: median.jpg
(Size: 272.36KB, Downloaded 1143 times)

[Updated on: Wed, 19 October 2011 09:39]

Report message to a moderator

Re: MEDIAN function on string??? [message #741215 is a reply to message #736113]

Wed, 19 October 2011 09:51

Dejan Vujanic

Messages: 67
Registered: October 2011

Member

Hi Jason. Thanks for your reply and effort. You are really helping me.
You send me great example and I did it all like you. My Median Minutes column is always correct calculated but medianstring column is always empty. I copy paste your example but it stays empty. I tried and with simple example to use that column as Float-- Median Minutes divided with 24 but it stays empty. Do you maybe see what am I doing wrong? Here is all the settings:
index.php/fa/4419/0/

Attachment: median.jpg
(Size: 272.36KB, Downloaded 987 times)

Report message to a moderator

Re: MEDIAN function on string??? [message #741275 is a reply to message #735945]

Wed, 19 October 2011 11:10

Dejan Vujanic

Messages: 67
Registered: October 2011

Member

Jason just to add-- I added new computed column which calculates from standard output column INCIDENTID and that works ok. But for similar expression medianstring stays empty. It's like medianstring cant pull out information from computed Median Minutes column. do you know the reason?
thanks for help

index.php/fa/4420/0/

Attachment: median2.jpg
(Size: 157.81KB, Downloaded 1016 times)

Report message to a moderator

Re: MEDIAN function on string??? [message #741449 is a reply to message #741275]

Wed, 19 October 2011 14:44

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

What happens if you change the type of medianstring to String and then
use this expression:

var mytime = row["MedianMinutes"];
tst = "Testing " + mytime

Jason

On 10/19/2011 7:10 AM, cankovicv wrote:
> Jason just to add-- I added new computed column which calculates from standard output column INCIDENTID and that works ok. But for similar expression medianstring stays empty. It's like medianstring cant pull out information from computed Median Minutes column. do you know the reason?
> thanks for help
>
>
>

Report message to a moderator

Re: MEDIAN function on string??? [message #742140 is a reply to message #741449]

Thu, 20 October 2011 08:05

Dejan Vujanic

Messages: 67
Registered: October 2011

Member

Hi Jason.

It's again empty.
But never mind I succeeded to present median string results. Solution was that I used only one computed column (Median Minutes) and on report medianstring column is not bound to medianstring computed column.I bound it with expression

var mytime = row["MedianMinutes"];
var mytime3=BirtMath.roundDown(BirtMath.divide(BirtMath.mod(mytime,1440),60),0);
var mytime2=BirtMath.roundDown(BirtMath.divide(mytime,1440),0);
mytime2 + "days " + mytime3 + " hours " + mytime%60 + " minutes";

Now it works OK. I just have only one question can I display this result just in one field-out of the table in some field. Because this result can be repeted n-times through the table as you can see in attachment. I saw also that in your example also there was repetition of result?? Can it be shown only once??

Thanks,best regards

index.php/fa/4435/0/

Attachment: median3.jpg
(Size: 211.42KB, Downloaded 962 times)

Report message to a moderator

Re: MEDIAN function on string??? [message #742494 is a reply to message #742140]

Thu, 20 October 2011 14:44

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Select the column on the table and look in the general properties.
There is a check box to suppress duplicates.

Jason

On 10/20/2011 4:05 AM, cankovicv wrote:
> Hi Jason.
>
> It's again empty.
> But never mind I succeeded to present median string results. Solution was that I used only one computed column (Median Minutes) and on report medianstring column is not bound to medianstring computed column.I bound it with expression
>
> var mytime = row["MedianMinutes"];
> var mytime3=BirtMath.roundDown(BirtMath.divide(BirtMath.mod(mytime,1440),60),0);
> var mytime2=BirtMath.roundDown(BirtMath.divide(mytime,1440),0);
> mytime2 + "days " + mytime3 + " hours " + mytime%60 + " minutes";
>
>
> Now it works OK. I just have only one question can I display this result just in one field-out of the table in some field. Because this result can be repeted n-times through the table as you can see in attachment. I saw also that in your example also there was repetition of result?? Can it be shown only once??
>
> Thanks,best regards
>
>
>

Report message to a moderator