21 Replies Latest reply: Jan 8, 2016 1:24 PM by Jim Chan

As I am woking on my script, i hit a problem. I realize i have use such calculation expression and i need to change my existing sum(count) to the below:

(Sum({<AUTO_FLG = {S}, F_RESULT={1}>} COUNT))  / (Sum({<AUTO_FLG = {S}, K_RESULT={1}>} COUNT))

Concatenate(Table_summary_1)

KPI_DATE,

sum(COUNT) as rc_count,

'Hit Rate (% in Cases)' as Items

Resident Table_1

where AUTO_FLG = 'S' and FRAUD_RESULT = '1'

GROUP BY KPI_DATE,'Hit Rate (% in Cases)';

Hi Jim,

Not sure if I understand your question. Can you please elaborate more your issue with a .qvw or a screenshot sample?

Regards,

MB

Hi,

I want to replace this expression

(Sum({<AUTO_FLG = {S}, F_RESULT={1}>} COUNT))  / (Sum({<AUTO_FLG = {S}, K_RESULT={1}>} COUNT))

to the sum(COUNT)

sum(COUNT) as rc_count,

how can i do that?

See, what I suggested you...

Now you can simply use sum(FinalResult)

Yes Balraj solution is correct you can shift your expression in the script side by this way..

try like this?

Concatenate(Table_summary_1)

KPI_DATE,

(sum(if(F_RESULT=1 ,COUNT))  / Sum(if(K_RESULT=1, COUNT))) as FinalResult,

'Hit Rate (% in Cases)' as Items

Resident Table_1

where AUTO_FLG = 'S' and FRAUD_RESULT = '1'

GROUP BY KPI_DATE,'Hit Rate (% in Cases)';

Make sure both F_RESULT & K_RESULT in same table

i will try bro! Sorry, my F_RESULT , is the same as FRAUD_RESULT.

at here :" where AUTO_FLG = 'S' and FRAUD_RESULT = '1'" - should i add in K_RESULT = 1 as well?

Rgds,

Jim

Yes...otherwise it will give an error

Hi Balraj,

Since it is about rating - i need to change it to percentage.

(sum(if(F_RESULT=1 ,COUNT))  / Sum(if(K_RESULT=1, COUNT)), #,##0% )  - is it correct?

Rgds.

Jim

use num() function

try like this?

num(YourExpression,'#,##0.00%')

=num(sum(if(F_RESULT=1 ,COUNT))/Sum(if(K_RESULT=1, COUNT)), '#,##0%'

Ar.... the num().. ok bro. Let me try that.

Thankssss! I will try it out!

Hello Balraj,

i have tried with this. not working.

num(sum(if(FRAUD_RESULT=1 ,COUNT)), '#,##0%') / num(sum(if(CHK_RESULT=1 ,COUNT)), '#,##0%') as rc_count,

Rgds,

jim

is it because of others expressions????

rgds

Jim

not this way dear..

try this:

=num((sum(if(F_RESULT=1 ,COUNT))/Sum(if(CHK_RESULT=1, COUNT))), '#,##0%'

replace F_RESULT with FRAUD_RESULT

oh dear, is not changing anything, eventhough i have changed to '##,####'

=num((sum(if(FRAUD_RESULT=1 ,COUNT))/Sum(if(CHK_RESULT=1, COUNT))), '#,##0%')

Balraj,

here's my sample files. i really cant change it. Pls look at my editor script.

Even you can try this as you said F_RESULT , is the same as FRAUD_RESULT...

Concatenate(Table_summary_1)

KPI_DATE,

(sum(COUNT))  / Sum(if(K_RESULT=1, COUNT))) as FinalResult,

'Hit Rate (% in Cases)' as Items

Resident Table_1

where AUTO_FLG = 'S' and FRAUD_RESULT = '1'

GROUP BY KPI_DATE,'Hit Rate (% in Cases)';

SUM(rc_count) / SUM(Total rc_count)