topic Re: Incorrect Expression in QlikView

Incorrect Expression

dandaanilreddy — Thu, 20 Oct 2016 19:48:42 GMT

Hello Qlik Developers

I have below expression in text box:

=num(sum(if(Reported_Out_actual.count_idTBD_actual<>0 and Reported_Out_actual.BillType='Awaiting Order Entry',Reported_Out_actual.count_idTBD_actual)),'###,###') the result should be 283+2 = 285 but here i am getting wrong result.

=num(

sum(if(Reported_Out_actual.count_idTBD_actual<>0 and Reported_Out_actual.BillType='Missing Ins.', Reported_Out_actual.count_idTBD_actual))

,'###,###') the result should be 203+4 = 207 this result is matching with the last 2 TBD types in the below table box.

I have a table box in which i have calculated dimension like =if(count_id=' ',Bill_Type_Details) and expression as

count(distinct Reported_Out_case_id) and the result is below:

TBD Type	Total Cases
Need Info To Be Eligible For Billing(MI)	283
Waiting for Billing Entry	2
Insurance Info Not Availaible or Invalid	203
Waiting for Insurance Code	4
Total	492

I tried the below expression but i am not getting the correct values;\

=count(DISTINCT if(Reported_Out_actual.count_idTBD_actual<>0,count_id='' and Bill_Type_Details='Need Info To Be Eligible For Billing(MI)'and Bill_Type_Details='Waiting for Billing Entry',Reported_Out_case_id))

Can someone help me please in getting the correct result.

Thanks

Anil

Re: Incorrect Expression

Oleg_Troyansky — Thu, 20 Oct 2016 20:24:14 GMT

Hi,

I think the problem is hiding here:

In your IF() function, after the first condition is validated and after the comma, count_id looks like the true_value. but then you continue the condition with AND...

=count(

DISTINCT if(Reported_Out_actual.count_idTBD_actual<>0, count_id='' and Bill_Type_Details='Need Info To Be Eligible For Billing(MI)'and Bill_Type_Details='Waiting for Billing Entry',Reported_Out_case_id))

Did you mean that as another condition, like this:

=count(DISTINCT if(Reported_Out_actual.count_idTBD_actual<>0 and count_id='' and Bill_Type_Details='Need Info To Be Eligible For Billing(MI)'and Bill_Type_Details='Waiting for Billing Entry',Reported_Out_case_id))

cheers,

Oleg Troyansky

Learn advanced Qlik techniqes in my book QlikView Your Business.

Re: Incorrect Expression

dandaanilreddy — Thu, 20 Oct 2016 20:27:01 GMT

Hi Oleg

Thanks for the reply. I have used your expression but its displaying zero.

Thanks

Anil

Re: Incorrect Expression

dandaanilreddy — Thu, 20 Oct 2016 20:57:47 GMT

I Got the result by writing the below expression;

=count(distinct(if(match(Bill_Type_Details,'Need Info To Be Eligible For Billing(MI)','Waiting for Billing Entry'),Reported_Out_case_id)))

Thanks

Anil