nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Is it possible to modify an aggregated value at a crosstab?

alkon

Hello

I have a list of phone calls. Each call characterized by specific type and duration in seconds.
It's necessary to make a brief report with a number of calls of each type and total duration but duration must be formatted as hh:mm:ss

I understand how to make a data cube and a crosstab using COUNT() and SUM() but how to convert the result of SUM() to the specified format (hh:mm:ss)?

Or, probably, it's better to use some other way ?

Any help is appreciated.

Find more posts tagged with

Comments

mwilliams

So, it's currently just an integer in seconds? What you'll probably need to do is to delete the measure element and put a data element in its spot with type Date, then create a new date with the following as the expression:

new Date(0,0,0,0,0,secondsMeasure);

Then, in the property editor under the formatDate, you can make a custom format of hh:mm:ss

alkon

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77513" data-time="1306270159" data-date="24 May 2011 - 01:49 PM">
So, it's currently just an integer in seconds? What you'll probably need to do is to delete the measure element and put a data element in its spot with type Date, then create a new date with the following as the expression: 
 
new Date(0,0,0,0,0,secondsMeasure); 
 
Then, in the property editor under the formatDate, you can make a custom format of hh:mm:ss </blockquote>
 
Thanks for your answer! 
 
Just to be sure that I understand you correctly you suppose to: 
 
1) Add ComputedColumn to DataSet as 
DataType=DateTime, Expression=new Date(0,0,0,0,0,row['secondsMeasure']) 
 
let's name it 'duration' 
2) At the DataCube use this column for aggregation as SUM(...) 
 
Is it possible to apply SUM(...) to DateTime column? Which Type is it necessary to specify for that aggregation? 
 
3) At the crosstab for the cell with SUM(...) column specify 
Format DateTime=HH:mm:ss 
 
Or I misunderstood smth?

mwilliams

Here is what I meant. This example was made in 2.6.2. Be sure to change the file location in the datasource to wherever you save the .txt file.

alkon

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77553" data-time="1306338079" data-date="25 May 2011 - 08:41 AM">
Here is what I meant. This example was made in 2.6.2. Be sure to change the file location in the datasource to wherever you save the .txt file. </blockquote>
 
Thnks!!! It's clarified how is it possible to modify the aggregated value of a DataCube. 
 
But seems it's not possible to make correct formatting of seconds (integer) to hh:mm:ss in case than the number of seconds higher than 24*3600 or near it. 
 
I've made the next expression (not sure is it the best and shortest one but it works): 
<pre class='_prettyXprint _lang-auto _linenums:0'><VALUE-OF>importPackage(Packages.java.util);
importPackage(Packages.java.math);

var formatter = new Packages.java.util.Formatter();

var hour = new Packages.java.math.BigInteger(parseInt(data["Duration_Group/CallType"] / 3600));
var minute = new Packages.java.math.BigInteger(parseInt((data["Duration_Group/CallType"] - hour*3600)/60));
var second = new Packages.java.math.BigInteger((data["Duration_Group/CallType"] - hour*3600 - minute*60));
var result = formatter.format("%1$d:%2$02d:%3$02d", hour, minute, second);

result;</VALUE-OF>
</pre>

mwilliams

You could always just calculate hours, minutes, and seconds out of the amount of seconds and make your own string time hours + ":" + minutes + ":" + seconds. This would allow for a count of hours more than a day.

alkon

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77562" data-time="1306352001" data-date="25 May 2011 - 12:33 PM">
You could always just calculate hours, minutes, and seconds out of the amount of seconds and make your own string time hours + ":" + minutes + ":" + seconds. This would allow for a count of hours more than a day. </blockquote>
 
Yes, but it's necessary to format minutes and seconds with trailing zero if less than 10.

mwilliams

This could easily be scripted.

//ToDo: determine integer values for hours, minutes, and seconds.

if (hours < 10){
hours = "0" + hours.toString();
}
else{
hours = hours.toString();
}
if (minutes < 10){
minutes = "0" + minutes.toString();
}
else{
minutes = minutes.toString();
}
if (seconds < 10){
seconds = "0" + seconds.toString();
}
else{
seconds = seconds.toString();
}

hours + ":" + minutes + ":" + seconds;

alkon

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="77566" data-time="1306356211" data-date="25 May 2011 - 01:43 PM">
This could easily be scripted. 
 
//ToDo: determine integer values for hours, minutes, and seconds. 
 
if (hours < 10){ 
hours = "0" + hours.toString(); 
} 
else{ 
hours = hours.toString(); 
} 
if (minutes < 10){ 
minutes = "0" + minutes.toString(); 
} 
else{ 
minutes = minutes.toString(); 
} 
if (seconds < 10){ 
seconds = "0" + seconds.toString(); 
} 
else{ 
seconds = seconds.toString(); 
} 
 
hours + ":" + minutes + ":" + seconds; </blockquote>
 
Agreed. 
Thanks again for the decision how to modify aggregated value.

mwilliams

Not a problem. Let us know whenever you have questions!