Skip to main content
Announcements
New: No-code data prep in Qlik Cloud Analytics™ TAKE A TOUR
cancel
Showing results for 
Search instead for 
Did you mean: 
dandaanilreddy
Partner - Creator III
Partner - Creator III

Incorrect Expression

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 TypeTotal Cases
Need Info To Be Eligible For Billing(MI)283
Waiting for Billing Entry2
Insurance Info Not Availaible or Invalid203
Waiting for Insurance Code4
Total492

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

1 Solution

Accepted Solutions
dandaanilreddy
Partner - Creator III
Partner - Creator III
Author

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

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Creator III
Partner - Creator III
Author

Hi Oleg

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

Thanks

Anil

dandaanilreddy
Partner - Creator III
Partner - Creator III
Author

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