cancel
Showing results for 
Search instead for 
Did you mean: 

CR - Getting 0.63 values all through.

Former Member
0 Kudos

For the Column "% of First Call Resolution" I have to calculate it based on the below formula

SUM(CASE WHEN INTERACTION_RESULT = 'FCR' THEN 1 ELSE 0 END) / COUNT(Interaction_ID)

For which, I wrote the below formula

Sum ({If {14CICustomerInteractions_query.Interaction Result} = "FCR" then 1 else 0}) / Count ({14CICustomerInteractions_query.No of Interaction(s)})

But I am getting 0.63 values all through.

Need Solution.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Create two formulas

one that does the 1 or 0 ( you can test this at the details level )

then do the calculation sum/count

Edited by: AlunJD on Jan 10, 2012 1:54 PM

Former Member
0 Kudos

Hi

Its not Working

Need solution.

abhilash_kumar
Active Contributor
0 Kudos

Hi Upaasana,

I remember your previous post and I'm guessing this is the same report. Although I'm not sure how the report is laid out, however here's something that you can try:

1) Create this formula and place this on the details section:

whileprintingrecords;
numbervar c;
If {14CICustomerInteractions_query.Interaction Result} = "FCR" then
c := c + 1;

2) Create another formula and place this one on the Group Footer to display the result:

whileprintingrecords;
numbervar c;
c/Count ({14CICustomerInteractions_query.No of Interaction(s)});

3) Create this formula to reset the variable and place this on the Group Header:

whileprintingrecords;
numbervar c := 0;

Hope this helps!

P.S: Remember, when you use functions like Sum(), count() etc it will summarize or count the field referred as a whole. Meaning if you have 10 customers and use count(), it will show you 10 no matter where you place it on the report.

Never use functions like Sum() in the details section when you're trying to do a running total. Either create a manual running total the way I suggested above or use CRs running total creation wizard.

-Abhilash

Former Member
0 Kudos

Hi

Thanks for Reply

But Its not working.

My Report consists of following columns

Workgroup , interaction reason1, interaction reason 2, No of Interactions, Average Interaction time, No of First call Resolution , % of First call Resolution

I have to get " % of First call Resolution" value for each & every record

Getting 0.63 value for all Records

Need solution

Edited by: upaasana on Jan 18, 2012 10:08 AM

Edited by: upaasana on Jan 18, 2012 10:27 AM

Former Member
0 Kudos

Hi

Need some help to solve this Issue

My Report columns are Workgroup, Interaction Reason 1, Interaction Reason 2, No of Interactions ,Average Interaction time ,No of First Call Resolution, % of First Call Resolution.

" % of First Call Resolution " has to be calculated for Each & Every Record

Getting 0.63 value for all records

Any Solution

Former Member
0 Kudos

Hi

Can you give your data example along with your fields ? It will be very easy for any one to understand your business logic.

Also when you are looking for record wise results you don't have to use sum() this will give you entire report summary. try without sum() and check results.

Thanks,

Sastry

Former Member
0 Kudos

Hi

Thanks For Your Reply

Source - Objects from Universe

My Report columns with examples workgroup (activation off ,boxoffice off ,call centre executive etc) ,interaction reason 1(billing ,order, billing complaint etc),interaction reason 2(new ,change pay means,complaint on future dated reconnection etc) ,No of Interaction (5,149,30 etc), Average interaction time (47,5,16 etc), No of First Call Resolution (5 ,4 ,30 etc) ,% of First Call Resolution (75.6 % ,56.2% etc) (reports on Dish tv setup boxes sales)

Getting 0.63% for all records for " % of First call Resolution"

Any Solution

abhilash_kumar
Active Contributor
0 Kudos

Hi,

Looking at your example, suppose, 'No. of Interactions' = 5 and 'No. of 1st call resolutions' = 5, then

'% of First Call Resolution' = 'No. of 1st call resolutions'/'No. of Interactions' the is equal to 100 %; Am I interpreting this correctly?

If you want to show this value on every detail line, then you could try this formula:

{No. of 1st call resolutions} / {No. of Interactions} & " %"

You can then drag this formula on the details section.

-Abhilash

JWiseman
Active Contributor
0 Kudos

by chance are you using a cross-tab? judging by the results in your report i am guessing yes. if you are there is different syntax for cross-tabs as cross-tabs behave differently than what's in the details or group sections as they are like pivots.

if so, what version of cr are you using? if cr2008 or cr2011 please see the new "grid" functions for cross-tabs for the proper syntax. if you go to the initial Start Page of crystal reports and then the downloads tab, go to the sample reports and make sure you download both sets of the cr 2008 reports...there's a couple different cross-tab samples in there and i believe that the one that is most applicable to this case would be a finance one showing a percent calculation using a calculated member.

if you are using an earlier version, i have some sample cross-tab reports [here |http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11346] [original link is broken] [original link is broken] [original link is broken];that use variables on one of the formatting dialogues in conjunction with the Display String property on the cell.

in cr2008 or cr2011 it is a lot easier though as there are many different functions built to assist you with cross-tabs.

cheers,

jamie

Former Member
0 Kudos

Hi

Thanks For Your Reply

Its not a Cross tab report.

Can You look into another post by me Regarding" CR Unknown database connector error Details: Failed to Retrieve SQL Error" & give some solution if possible.

JWiseman
Active Contributor
0 Kudos

thought i'd take a guess there about the x-tab.

however, if you look at your initial formula, it uses report level summaries...

SUM(CASE WHEN INTERACTION_RESULT = 'FCR' THEN 1 ELSE 0 END) / COUNT(Interaction_ID)

this is going to take the sum across the entire report and the count across the entire report.

what you should look into is group summaries. where do you want this calculation to be evaluated on...i.e. where do you want the number to change...is it on Workgroup?

1) if so, create a group on the Workgroup field or on the field that you wish to have the summary % based on

2) insert a summary on the group level for both the 1 else 0 formula and for the count of ID

3) now create a new formula that does the % on the group level...something like

SUM(CASE WHEN INTERACTION_RESULT = 'FCR' THEN 1 ELSE 0 END, {your group field}) / COUNT(Interaction_ID, {your group field})

nikhil_joy2
Active Contributor
0 Kudos

Hi Upaasana,

in the formula you have given you are returning flag value. ie 1 or 0. That wont added up. Instead of that try the below logic.

COUNT(CASE WHEN INTERACTION_RESULT = 'FCR' ) / COUNT(Interaction_ID).

you can introduce a number variable c like below.

 numbervar c;
If {14CICustomerInteractions_query.Interaction Result} = "FCR" then
c := c + 1;

divide the value c with count of interaction ID. you will get the answer.

modify your formula according to the above logic and try.

hope it helps.

Regards,

Nikhil Joy