String Conversion and Count 1

[gennaroalpha7]

Hello Experts -

I have a field,{CallLog.RecvdDate}, that is not a number field. I would like to convert it from a string to a number field. And then I would like to provide a ticket count for the current day that the analyst(s) have opened. I am also going to use a summary count too. My question(s), if you can please help out.

I am using a formula for this task because I want a separate task - my question(s): Is it more appropriate to use 1 formula or 2 formulas to get this done? And, can you please review my the current formula that I have and give some feedback.

Here what I have so far - when I check this formula it highlights 'CurrentDate' and says 'A String is Required Here'. I am using CR8. The first part is the conversion and the second part is the counting.

datetime (date({CallLog.RecvdDate})) and

if {CallLog.RecvdDate} = CurrentDate

then 1 else 0


Thanks for the help.

G.

 

[BettyJ]

The error is because you are trying to compare a string field ({CallLog.RecvdDate} ) with a date field (currentdate).

Either you can convert your field to a date
[tt]if cdate({CallLog.RecvdDate}) = CurrentDate[/tt]

or convert currentdate to string
[tt]if {CallLog.RecvdDate} = totext(CurrentDate)[/tt]

 

[gennaroalpha7]

Hi Betty -

Can I use the Convert and Count in the same formula?

Such as:

if cdate({CallLog.RecvdDate}) = CurrentDate then 1 else 0
and
Count ({CallLog.RecvdDate}) in CurrentDate

After checking, it highlights 'CurrentDate' and says a number range is required here.


Thanks

G.

 

[kray4660]

I am not sure how you can compare a count to the current date?!? What are you trying to achieve?

 

[gennaroalpha7]

Hi -

I am trying to count the tickets per analyst for the current day. But my ({CallLog.RecvdDate}) is in a String format. So I was thinking of converting the string and then counting the, {CallLog.CallID}, tickets for the current day. Please offer feedback as to how to go about this transformation.

Thanks.

G.

 

[BettyJ]

In the record selection give the condition
cdate({CallLog.RecvdDate}) = CurrentDate

Create a group on Analyst (select your field).
Insert --> Summary
Choose the field to summarize
{CallLog.CallID}
calculate this summary
Count
Summary Location
Group Analyst (select your field)

 

[SkipVought]

Please post an example of this string formated "date".

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gennaroalpha7]

Hi -

The formated Date is 2014-03-25

Thank you for your reply Betty, but I would like a separate formula for this transformation and count. Because I have other stuff in the record selection, and if I add the, cdate({CallLog.RecvdDate}) = CurrentDate,
above it messes up what I currently have.

Thanks.

G.

 

[BettyJ]

You will have to create a group on Analyst. In the group header of this group, create a formula to initialize a variable, for eg.
@initcount
[tt]whileprintingrecords;
numbervar countperanalyst:=0;[/tt]

Then create another formula and place it in details.
@CountPerAnalyst
[tt]whileprintingrecords;
numbervar countperanalyst;
if cdate({CallLog.RecvdDate}) = CurrentDate then
countperanalyst:=countperanalyst+1
else countperanalyst:=countperanalyst;
countperanalyst;[/tt]

Create a third formula
@DispCountPerAnalyst
[tt]whileprintingrecords;
numbervar countperanalyst;
countperanalyst[/tt]

Place it in the group footer of Analyst field.

 

[gennaroalpha7]

Hi Betty -

It's amazing, but it works! Thank you!

After I placed the @initcount formula in the Group Header of the Analyst I suppressed it because it was just showing zeros throughout.

If wanted to create a total count of the tickets opened today, would I insert a summary count on the CountPerAnalyst formula or would i need to create another formula?

I have been working with CR for about 1.5 years and I have all these ideas in my head, but when i try to use them they come out jumbled. There's so much to learn.

Thanks for your help.

G.

 

[BettyJ]

@initTotalCount;
[tt]whileprintingrecords;
numbervar totalcount:=0;[/tt]
Place it in Report Header (and suppress)

@TotalCount
[tt]whileprintingrecords;
numbervar totalcount;
numbervar countperanalyst;
if cdate({CallLog.RecvdDate})=currentdate then
totalcount:=totalcount+1;
totalcount;[/tt]
Place it in details (and can be suppressed later after making sure it is counting correctly.)

@DispTotalCount
[tt]whileprintingrecords;
numbervar totalcount;
totalcount;[/tt]
Place it in Report Footer

 

[gennaroalpha7]

Hi Betty -

When I placed @DispTotalCount in the report footer i received a single small number and nothing else on the report. Because next to the analyst name I would like a count of the tickets opened only for the current date. So then I placed @DispTotalCount in the Group Footer of the Analyst, then i recieved ticket numbers. To get a summary count of today's opened tickets do I summarize on @DispTotalCount?

Thanks.

G.

 

[BettyJ]

In one of my previous replies (25 Mar 14 12:13), I had mentioned using the formula,
@DispCountPerAnalyst
whileprintingrecords;
numbervar countperanalyst;
countperanalyst

and to place it in the group footer of Analyst field. This will give you the what you are looking for. DispCountPerAnalyst gives you the total count of all tickets opened today by a particular analyst.

I thought you needed the grand total(all analysts)of all the tickets opened today by all analysts. It will be the sum of all DispCountPerAnalyst values. If you do not need the grand total please ignore my previous reply.

Hope this helps. Any questions, let me know.

 

[gennaroalpha7]

Hi Betty -

Yes, I need the total tickets that all the analysts opened for today (currentdate), and also the individual counts of each analyst next to their names. so it would look like this...

Smith 12

sanchez 6

Farve 10


Total tickets: 28

Which of your post do i follow, tbe previous one or the one before that? Which one is best for my situation. I think they will both work, but can I insert a summary count?

Thanks Betty.

G.

 

[BettyJ]

You will only have to use the formulas in both posts. The first one is for the total count per analyst and the second post is for the total count of all analyst. After that you have to suppress the details if you need the output as you listed in your last post. I believe, that's all you probably will need to do now (just suppressing the detail section)

Anything else, feel free to ask.

 

[gennaroalpha7]

Hi Betty -

Here's a look at the report for your reference.

Please follow this link...

https://www.dropbox.com/s/ugzg2qing09l84z/Call_Queue_Report_v0.4_development.rpt

Thanks.

G.

 

[gennaroalpha7]

Hi Betty -

When I add @DispTotalCount, per post 25 Mar 14 13:58, its not giving a total count. I am using all the formulas from both posts. And there both essentially giving the same information.

Did you get a chance to view the report?

Thanks.

G.

 

[BettyJ]

I did not view the report.
Can you make sure you placed @initTotalCount in the Report Header.

 

[gennaroalpha7]

Hi Betty -

When I placed the @initTotalCount in the Report Header the @DispTotalCount gave me what I needed. So the @initTotalCount and the @DispTotalCount are both in the Report Header and work together. When I remove the @initTotalCount back into the Group Header #1 of the Analyst, which Analyst is the {CallLog.Tracker} table the @DispTotalCount returns a small number, not a total.

Thanks for your amazing help Betty. Now I will validate the numbers. I will study the code you have given me.

Have a nice day and a toast and a smile for you.

G.