complex formula - convert number of minutes to DD:HH:MM and chart it?

[Maven4Champ]

I have a column type of "NUMBER" and it's called
{TABLE.TTCM_DEAL}. It's basically the number of minutes from one starting point to one ending point done in an Oracle SQL view.

I am using Crystal 8.5 against an Oracle Database.

I want to convert my number of minutes to DD:HH:MM but also where I can chart the result in a chart in descnding order from highest TTCM_DEAL duration to lowest TTCM_DEAL duration but also show the dealer associated with that duration.

My data looks like this:
[DEALER] [TTCM_DEAL]
ABC CO. 151
ABC CO. 23
ABC CO. 117
DEF CO. 49
DEF CO. 57

I would want to add um the TTCM_DEAL for ABC CO. Dealer and then divide by the count (3) to get an average number.

I then want to chart this average number as DD:HH:MM.

In this representation it would like this this in the chart:

ABC CO. [============================]
DEF CO. [=====================]
etc. etc. etc.

Please tell me if I am out of my mind and should just do this in excel as I have been for the past year?

 

[Maven4Champ]

I can take the silence in this thread as an indication that this may not be possible in the Crystal reporting tool.

I think I found a thread from lbass on how to convert a number to time but I continually got a string is non-numeric error and not even sure if that was the appropriate route to take.

Please reply if you have any help - if not, I will chalk it up to inexperience and continue doing the report in Excel.

Thanks for the assistance to all!

 

[Tatertot45]

Well... The only time i have seen something like this was in my Crystal class. We had a problem that was similar to this. We used an array and variable to build the formatted time value you are looking for. It was not easy, and i can't do it again. I can tell you that we figured out how many whole hours there were for the minutes figure, then carried the remainder over with a variable and figured out how many whole minutes there were and carried that remainder over with a variable and then figured seconds. Meanwhile, if i remember correctly, we "stored" the whole HH, MM, SS values in arrays and displayed them together eventually. I'm not sure of your application, but it might be worthwhile trying to accomplish your mission by using hours or days, with the remainders as decimals. like 2,160 minutes is 36 hours or 1.5 days. That might be easier than trying to run minutes out to HH:MM:SS. Wsh i could be more help.
The only other thing i could think of would be to look at what you do in a spreadsheet and what you do in crystal, and try to advance your crystal report further and further as days go by. Start with a straight data dump, exported to excel. then use a few formulas to get the fields refined, then more fields and formulas, then work on a calculation for HH:MM:SS usine variables and arrays. I find that plugging away a little at a time can lead to an eventual solution. Baby steps...

Hope this helps...

 

[Maven4Champ]

Does anyone know how excel charts its numbers as time. What I have done previously is this...

Exported my dd:hh:mm formula (string) along with the dealer names to Excel. I then create a new column to the right and do VALUE=(A2) for instance (assuming my dealer time value is in cell A2. This then converts that dd:hh:mm to a number with many decimal places. I simply right click and choose format cell and choose the dd:hh:mm format in Excel and boom - I am in business.

I think if I could either A.) figure out how to convert my dd:hh:mm to an actual date instead of a string or B.) convert my dd:hh:mm string to a number a la Excel, I could then chart that and change the scale in the chart within Crystal to time format and it should work I think.

I have already solved the "convert to dd:hh:mm using arrays as you mentioned but again, it's a string. I can post the formula when I get to work later today. As you can tell this has kept me up till 1:30AM in the morning my time - OUCH.

Regardless, I believe in the Crystal tool and the support community here but again I chalk this up to inexperience on my part.

 

[Lukus]

Hey,
i've jsut had the same sorta problem and found a very useful answer on the thread thread767-806045
Joycr9 has a fix for your format display, and i have one for your average:

quote from Joycr9:
-----------------------------------
//Convert Total Seconds to DD:HH:MM:SS
//Remove comment tags to format in
//Days X, Hours X, Minutes X, Seconds X
//and add comment tags or delete DD:HH:MM:SS formating.

NumberVar TotalSec := datediff("s",NthLargest (1, {@open_time}, {SERVICE_CALL.SERVICE_CALL_ID}), ({@min_vendor1}));
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

//comment or delete the following to format it
//with the text instead of looking like 12:05:21:05
ToText ( Days, '00', 0,'') + ':'+
ToText( Hours, '00', 0,'') + ':'+
ToText ( Minutes,'00', 0,'') + ':'+
ToText( Seconds,'00', 0,'')

//'Day(s) ' + Totext ( Days, '00', 0,'') + ' ' +
//'Hour(s) ' + Totext ( Hours, '00', 0,'') + ' ' +
//'Minute(s) ' + Totext ( Minutes,'00', 0,'') + ' ' +
//'Second(s) ' + Totext ( Seconds,'00', 0,'')


-------------------------------------
so, your TotalSec variable will be your
**({TABLE.TTCM_DEAL}*60);**
INSTEAD OF
**datediff("s",NthLargest (1, {@open_time}, {SERVICE_CALL.SERVICE_CALL_ID}), ({@min_vendor1}));**

this will nicely format your minutes out to 00:00:00:00

for your average, you can make a formula to total all the seconds, a formula to count how many rows there are, and one to find the average (total / nrows).

Then if you make a new formula, copy and paste Joycr9's fix in there again but this time replace the totalsec vaiable with your average field it will format out your average too.


sorry if this sounds a little like jibberish... made sense to me the first time

cheers,
Lukus

 

[Maven4Champ]

OK we are making great progress. I have so far a dd:hh:mm string (again) in my report using the formulas shown below...

//{@TTCM_AVG}
// CONVERTS MINUTES FROM SIEBEL VIEW TO SECONDS FOR CALCULATING DD:HH:MM
AVERAGE({TABLE.TTCM_DEAL}, {TABLE.DEALER_NAME})*60

// {@TTCM_DEAL}
//Convert Total Seconds to DD:HH:MM
//Remove comment tags to format in
//Days X, Hours X, Minutes X
//and add comment tags or delete DD:HH:MM formating.

NumberVar TotalSec := {@TTCM_AVG};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);

//comment or delete the following to format it
//with the text instead of looking like 12:05:21
ToText ( Days, '00', 0,'') + ':'+
ToText( Hours, '00', 0,'') + ':'+
ToText ( Minutes,'00', 0,'')


------------------------------------------------------

I am now getting a pure dd:hh:mm as a string but I need to chart that somehow if possible...

My other idea as stated above was to convert this to a number as Excel would that would then be converted to a dd:hh:mm in my chart.

My main problem is now, the @TTCM_DEAL cannot be charted when I attempt to - its grayed out.

 

[Maven4Champ]

I have also figured this much out.

When putting 31:04:42 (hh:mm:ss to appear properly in excel as it does in Crystal for dd:hh:mm) in Excel and converting to a datetime, it shows me: 12/31/1899 07:04:42

What I assume is that it plots a date time from 12/30/1899 and adds however many hours:mins:secs from that point.

For example, 12/30/1989 12:00AM plus 31 hours 4 minutes and 42 seconds would equal 12/31/1899 07:04:42 because 31 hours - 24 hours = 1 day. Add that day to 12/30 and you get 12/31. The reamining hours is 7 which is added acorrindlgy and lastly minuts and seconds.

Is there a way to emulate this behavior in Crystal (converting a string dd:hh:mm to a date time from 12/30/1899 12:00AM to possibly trick Crystal into plotting this on a chart as a time scale instead of number scale?

 

[Maven4Champ]

One last update and I am done for the morning until I actually go into work in 3 hours...

I have the following formula which basically does what I stated above. You will notice in the datetime function I am calling other formuals - they are basically stripped down versions of my original dd:hh:mm formula but in total hours and total minutes:

//{@TIME_FROM_1899} - Converts DD:HH:MM string to a datetime from the date of 12/30/1899 12:00:00AM
DateTimeValue (1899, 12, 30, 00, 00, 00) + (DateTime (0000, 00, 00, {@TOTAL_HOURS}, {@TOTAL_MINUTES}, 00))

However this fails at DateTime function stating a number is required here. Can you not supplement the actual HH and MM value with pre-formated number formulas? Maybe that's where I am confused.

TOTAL_HOURS and TOTAL_MINUTES are both numbers using ToNumber function already.

 

[Maven4Champ]

Does anyone have any ideas here. I am still working torwards a resolution but to no avail yet.

Let me know.
Thanks!

 

[lbass]

I think the issue is that you can only chart on a number, and so for that purpose, you should be using your original number field, or a formula that converts it to fractions of hours or days, etc., so that you can actually use an average in the chart. The problem is that as far as I know there is no way within CR to convert the result in the chart to the string display DD:HH:MM:SS that you want.

You could display a crosstab below the chart that shows both the minutes value and the converted string display as a sort of key to the chart. You would use the "on change of" field as your row field, and then add your the average of your number field as your summary. Then add it a second time. Go to the customize style tab and check "horizontal display" for summary values, and "show label". Then go into preview mode and select the second summary->right click->format field->common tab->display string->x+2 and enter:

whileprintingrecords;
numbervar x := currentfieldvalue; //this assumes x is the number of minutes
numbervar days := truncate(x,1440);
numbervar hrs := truncate(remainder(x,1440)/60);
numbervar mins := truncate(remainder(x,60));
numbervar secs := if mins <> 0 then
remainder(remainder(x,60),mins)*60;
totext(days,"00")+":"+totext(hrs,"00")+":"+
totext(mins,"00")+":"+totext(secs,"00")

You could also check to see whether any third party charting CR add-ons might have the capacity you're looking for--check

http://www.threedgraphics.com,

for one possibility.

-LB

 

[Maven4Champ]

lbass,

Is it possible to display one thing for data in a chart but chart it on something different?

For example,

I have a calculation called {@minutes+dealername} which combines the total minutes and dealer name so that when charting, it sorts the data descending based off total minutes (something that isn't in the chart because I am doing a stacked bar chart which actually divides up the total minutes into individual bars.

I then want to display just the dealer name but not the raw minutes in front of it.

So is it possible?

Here is an example:

6340050 DLR1 DEALER NAME
48240.33 DLR2 DEALER NAME
4412897 DLR3 DEALER NAME

the end result I would like is for it to remain in that sorting but display simply as:

DLR 1 DEALER NAME
DLR 2 DEALER NAME
DLR 3 DEALER NAME

I can't do the order/sort feature in the chart because the value I am needing to sort by (total minutes) isn't in the chart per say. Rather in the chart's data is the average of the individual cycles which ultimately add back up to the total minutes.

 

[Maven4Champ]

lbass,

I apologize but I am using CR8.5. Is the display string feature in CR 8.5.

I figured out how to do the charting now but I had to involve some SQL on the back-end to get it done. It's ugly but it works.

No scale but I want to try putting a crosstab below each chart so people can visually see their numbers - but not sure that this feature is available in CR8.5. If not, is there a workaround?

 

[lbass]

Using a rough workaround, you could display the number of minutes and then the dd:hh:mm:ss display below it. Does that work for you?

-LB

 

[Maven4Champ]

lbass,

Thanks for all the replies.

Can you clarify on your last post about the rough workaround?

I am also trying to show this in Crosstab, but it would need to look like this at a minimum:

DEALER NAME TA1 TA2 TA3 TA4
DEALER 1 1.56 2.94 56.76 5.98


Where TA1 would be my first total minutes average and TA2 would be my second, etc.

For that, I have a formula which divides:
({TABLE.TTCM_CREDIT} / 60 / 24)
And since this is already an average minutes in my database, I simply perform the calculation above to get Avg Days in decimal format.

However when I do a crosstab and insert those formulas, I get this result:

DEALER TOTAL
DEALER 1 1.56
2.94
56.76
5.98

It's not displaying it across which is really the end result that I need that may satisfy my users.

Let me know.
Thanks!

 

[lbass]

CR 8.5 doesn't allow horizontal summaries in crosstabs, and I'm assuming that TA1, TA2, etc., are separate summaries. The workaround I could provide would have allowed you to show the minutes and then the string display below it.

If you place the chart in the report header, you could use the body of the report to show the summary values without using a crosstab.

-LB

 

[Maven4Champ]

Your last reply was what I came up with when realizing Crosstab wasn't the way. I simply added in a sub-report that shows the values using the following formula:

--------------------------------------------------------
// {@TTCM_CREDIT}
NumberVar totalmins := {V_C2C_ROLLUP.TTCM_CREDIT};
NumberVar dys := totalmins \ 1440;
NumberVar hrs := (totalmins mod 1440)\ 60;
NumberVar mins := totalmins mod 60;

ToText (dys, 00) & "d:" & ToText(hrs, 00) & "h:" & Right("00" & ToText(mins, 0), 2) & "m";
--------------------------------------------------------

Doing this gave me my 00d:00h:00m that I am looking for. I did that for each of the TA1, TA2, etc. They will still need to reference the numbers when looking at the chart since I removed the timescale but at least we have an autoamted way of showing the stats visually but still showing the data behind those stats for the more analytical folks.

Thanks for all your help.