Incorrect Expression

[dandaanilreddy]

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

[Oleg_Troyansky]

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.

[dandaanilreddy]

Hi Oleg

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

Thanks

Anil

[dandaanilreddy · Accepted solution]

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