topic Set Analysis for nested if in QlikView

Set Analysis for nested if

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the below expression and I need to write this expression for distinct Accession_ID. For the count its simple and I gave Distinct keyword. But for the sum expression it is a bit complex and I need help to resolve it.

((sum(if(Payor_ID = 'MDTX' and Adjudicated=0 and High_Risk_NIPT like 'Yes' and (Test_Family_2 = 'NIPT' or Test_Family_2 = 'NIPTMD'), High_Risk_Medicaid*.8,

if(Payor_Group_3 = 'State Medicaid' and Adjudicated=0 and High_Risk_Medicaid = 'yes' and (Test_Family_2 like 'NIPT' or Test_Family_2 like 'NIPTMD'), High_Risk_Medicaid)))

+sum(if(Payor_Group_3 = 'State Medicaid' and Adjudicated=0 and Test_Family_2 = 'HCS',HCS_Medicaid)))/Count(Accession_ID))*%Medicaid_attainable

+((sum(if(Payor_Group_3 = 'Managed Medicaid' and Adjudicated=0 and High_Risk_NIPT = 'yes' and (Test_Family_2 = 'NIPT' or Test_Family_2 = 'NIPTMD'), High_Risk_Medicaid))

+sum(if(Managed_Medicaid_Credentialed = 'Managed Medicaid' and Adjudicated=0 and Test_Family_2 = 'HCS',HCS_Medicaid)))/Count(Accession_ID))* %Medicaid_attainable

Please help me finding a solution.

Thanks in advance.

Thanks,

Rohini. M

Re: Set Analysis for nested if

Anil_Babu_Samineni — Tue, 18 Oct 2016 02:35:15 GMT

Try Like This

(sum({<Payor_ID = {'MDTX'}, Adjudicated= {'0'}, High_Risk_NIPT = {'Yes'}, Test_Family_2 = {'NIPT','NIPTMD'}, Payor_Group_3 = {'State Medicaid'}, High_Risk_Medicaid = {'yes'})>} High_Risk_Medicaid) * 0.8))

(sum({<Payor_Group_3 = {'State Medicaid'}, Adjudicated=0, Test_Family_2 ={'HCS'}>} HCS_Medicaid) /Count( DISTINCT Accession_ID) *%Medicaid_attainable)

(sum({<Payor_Group_3 = {'Managed Medicaid'} and Adjudicated= {'0'}, High_Risk_NIPT = 'yes' , Test_Family_2 = {'NIPT' ,'NIPTMD'} >} High_Risk_Medicaid))

(sum({<Managed_Medicaid_Credentialed = {'Managed Medicaid'}, Adjudicated={'0'}, Test_Family_2 = {'HCS'} HCS_Medicaid)/Count(DISTINCT Accession_ID)* %Medicaid_attainable)

(sum({<Payor_ID = {'MDTX'}, Adjudicated= {'0'}, High_Risk_NIPT = {'Yes'}, Test_Family_2 = {'NIPT' ,'NIPTMD'}, Payor_Group_3 = {'State Medicaid'}, High_Risk_Medicaid = {'yes'})>} High_Risk_Medicaid) * 0.8))

(sum({<Payor_Group_3 = {'State Medicaid'}, Adjudicated=0, Test_Family_2 ={'HCS'}>} HCS_Medicaid) /Count({<Payor_Group_3 = {'State Medicaid'}, Adjudicated=0, Test_Family_2 ={'HCS'}>} DISTINCT Accession_ID) *%Medicaid_attainable)

(sum({<Payor_Group_3 = {'Managed Medicaid'} and Adjudicated= {'0'}, High_Risk_NIPT = 'yes' , Test_Family_2 = {'NIPT' ,'NIPTMD'} >} High_Risk_Medicaid))

(sum({<Managed_Medicaid_Credentialed = {'Managed Medicaid'}, Adjudicated={'0'}, Test_Family_2 = {'HCS'} HCS_Medicaid)/Count( {<Managed_Medicaid_Credentialed = {'Managed Medicaid'}, Adjudicated={'0'}, Test_Family_2 = {'HCS'} DISTINCT Accession_ID)* %Medicaid_attainable)

Re: Set Analysis for nested if

Tue, 18 Oct 2016 19:10:17 GMT

I made some changes for your modified code and it worked
Thank you!

Re: Set Analysis for nested if

Anil_Babu_Samineni — Wed, 19 Oct 2016 01:23:44 GMT

Good, Close this Thread if you are satisfied the answer by using flag Correct Answer.